I was reading the questions at http://community.powerbi.com today and saw an interesting question. In short the OP wanted to know if there was a way to identify (via a measure) if the row in a table had a colour =”Blue”, but also to check that there were no other rows with the same ID. In other words, the row had to be colour=”Blue” and also there had to be no other rows for the same ID in the table.
Here is the original post
The OP was pretty sure it couldn’t be done with a measure. I was pretty sure it could so I set about creating a measure to demonstrate.
How I Write Measures (tricky ones anyway)
This blog post is about “how” I solved this problem. I have blogged about my process previously here, although that was a calculated column.
What I Knew Already
- I knew that it was possible to filter the entire underlying table based on the ID in the row.
- I knew there was a CONTAINS function, although I had never used it before – it seemed like a likely candidate
Killer Tip 1: Create Good Test Data
The first thing I did was to replicate the test data shown above. As I have mentioned many times, good quality test data is essential to getting a quick correct answer to your problem. The data from the OP looked pretty good as it had covered the relevant scenarios (1 ID had just blue, 1 ID had blue and red, several IDs had no blue – this is good test data).
Killer Tip 2: Set Up a Table (or Matrix) to See the Results
After loading the data in Power BI Desktop, I immediately set up a table on the report canvas. This is fundamental to good measure writing and testing. If you can’t “see” how your measure reacts to your test data, you will struggle to make progress. So I simply loaded a table like this.
Killer Tip 3: Break the Problem Down
Don’t try to solve world peace on your first attempt. Break your DAX problem down into manageable pieces and solve a little bit of the problem as you take each step forward. Here is what I did.
First Test Measure
The first thing I did was to write the FILTER function and used it inside a CALCULATE as you can see below.
At first sight I was a bit surprised that I got the answer 1 all the way down the table – I was expecting 2 for the rows with ID 3. But then I quickly realised what was happening. I learnt this when I attended Marco Russo’s Mastering DAX class. In short, if a table doesn’t contain a primary key, then it will use the concatenation of all columns as a substitute primary key (quite complex and out of scope for this blog). Anyway, this clearly demonstrates my point about breaking the problem into pieces. Before moving on, I could “see” this formula was wrong.
Second Test Measure
I knew exactly how to fix the problem, by adding ALL around the table. Here was my second measure.
Now the ALL function around the table removes the filter applied by the concatenation of ID and Colour before reapplying a filter just on the ID column. Now it is clear that my filter is working correctly.
Third Test Measure
Now I had the FILTER working, it was time to check out the CONTAINS function.
Killer Tip 4: Read the Intellisense
As I wrote the measure, I just used Intellisense to guide me (no online documentation needed here).
See above how Intellisense provided me the syntax as well as a text description of how the function works. I knew immediately this was the right function. I replaced the COUNTROWS interim test portion with the CONTAINS function as shown blow.
Measure 3 was now telling me if the ID had at least 1 row where colour = “blue”.
The last thing I needed to do was modify the function so that it returned TRUE if and only if the current row was Blue and there were no other rows for that ID. That seemed pretty straight forward. If there is only 1 row for that ID, and if the colour of the ID was blue, then I was done. Here is my final measure.
I just wrapped the CONTAINS statement inside and AND, and added a second condition to see if there was only 1 row in the table.
I hope this walked through example and killer tips help you become more self sufficient with your DAX. Here is my workbook only blue.