Synchronising your double axes charts so that your two or more measures share the same scale is something almost everyone uses at some point. But sometimes, almost randomly, the Synchronise Axis option is greyed out. Why?! And why is it random?
This problem had annoyed me for many a month before I finally decided to try and work out how to fix it. And it turns out it is ridiculously easy to fix.
Most data analysts will admit that up to 80% of their time is spend doing “data prep”, sorting out the data that will be analysed and presented to the end user. So anything that we analysts can do to reduce that time means more actual analysis, right?
For me, part of this data prep involved joining lots of reports together, adding variables manually that I would later use in Tableau. It takes ages and is likely to incur human error into your data. Tableau’s ability to union your data together is a massive time saver so I’m quickly going to show you how its done.
Transform a crosstab format table with multi-row headers.
As Excel users we like to make some really odd looking tables. They make perfect sense to us when we look at them but, when you try and use them as a data source, Power BI struggles to understand them.
At work I recently needed to use an Excel workbook that had a table of data in crosstab format that had multiple header rows. I’m sure you’ve seen tables like it before:
Usually on data like this you’d use the unpivot command to get all of your values into one column but having multiple headers makes this a bit more tricky. Continue reading “Dealing with multi-row headers in Power Query”
In my previous post about Smarter Wildcard Matching, I showed you how to use a Tableau parameter to improve the capabilities of search filtering in large sets of data. In this post I will show you how to add a partial match to this, where your data matches one or more of your search terms but not all of them.
Continue reading “Absolute and partial match filtering”
Tableau method for wildcard filtering using multiple search terms.
Tableau’s Wildcard Match and Custom Value List filters are all well and good but they are limited. Both of them require that your search terms are in the correct order and the Custom Value List makes you click each individual item you want to include. For example, searching for “A4 Paper” would not return the [Product Name] “Paper A4”. This is an especially big problem if your field contains thousands of different values with little or no hierarchy to make your search easier.
This is where this Tableau trick can help. It allows you to search for multiple strings in any order, and Tableau will return a list of all items that contain all of those strings.
Continue reading “Smarter Wildcard Match Filtering”
In my last post (Filtering on multiple columns using one slicer) one of the steps I took was to remove duplicate entries from a list. The M language that Power Query and Power BI use is case sensitive. This means if you try and remove duplicates from a list and you have the same text but with different capitalisation, it will keep both of these entries when you try and use remove duplicates. This can cause some issues so lets look at how to fix it. Continue reading “Power Query M and case sensitivity”
Power BI method for filtering on multiple columns with one field using only relationships and no DAX.
I’m rebuilding my main report in Power BI. This was the report I created when I first started learning to use Power BI so became quite a mess as I learned to do new things and added in new data sources. I’m rebuilding this from scratch so is a good time make some posts about the pitfalls I’m avoiding and some new techniques I’ve implemented.
The users want to be able to select a name from a slicer to view only information relating to that person’s sites.
I have a fact table that contains financial results by site. I have an estate map that gives further details for the site, including names of people who are related to that site. The Contract Director, the Regional Director, the Regional Manager, the Management Accountant, etc.
Each of these is a column so I need to be able to filter on any one of these columns using a single slicer. A person could also appear in multiple columns so I need to be able to show a site if the person’s name appears in any column.
Continue reading “Filtering on multiple columns using one slicer”