Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
So I know that the scenario I'm about to describe can rather easily be done by creating summary tables and joining them, etc. But we have a particular situation where we're trying to see if we can do this with DAX measures and nothing else. I have a feeling it can't be done because it violates the basic premise of how a measure works... but just wanted to put it out there in case someone has an idea.
So let's say I had a simple table that looked like this below. So each ID has one or many colors. I want to know flag occurences of where an ID has ONLY the color Blue.
So I want to end up with something like this. And again, the exercise is to see if it's possible to do with with only Measures and nothing else.
I feel like this won't work, because when you write the Measure, I don't think it can examine rows other than itself. So I can't write a measure at the row level that would know that ID #3 has both blue AND red rows, right?
Hoping to confirm that, or maybe I'm just missing something.
Thanks
Thanks all!
Solved! Go to Solution.
Hi, try with this measure:
Does ID Have Only Blue? = IF ( HASONEVALUE ( Table1[ID] ), IF ( SELECTEDVALUE ( Table1[Color] ) = "Blue", IF ( COUNTROWS ( FILTER ( ALL ( Table1 ), Table1[ID] = SELECTEDVALUE ( Table1[ID] ) ) ) = 1, "Yes", "No" ), "No" ) )
Regards
Victor
Lima - Peru
Hi @GTS_ONE,
Will this do?
How about this
Measure = CALCULATE ( AND ( CONTAINS ( 'Table', 'Table'[Colour], "Blue" ), COUNTROWS ( 'Table' ) = 1 ), FILTER ( ALL ( 'Table' ), 'Table'[ID] = SELECTEDVALUE ( 'Table'[ID] ) ) )
Hi Matt,
Thanks for the quick reply...
But sorry, can you help me understand what that first "AND" is doing there? I'm not quite sure I follow that one...
@GTS_ONE wrote:
But sorry, can you help me understand what that first "AND" is doing there? I'm not quite sure I follow that one...
You secenario requires 2 things to be true.
1. The row must have blue as the colour.
2. Assuming 1 is true, there must also not be another row in the table with the same ID
the AND function is checking for both of these separately. the CONTAINS checks if the current row is blue, the second parameter of AND (countrows(table) checks if there are other rows that exist.
Hi Matt,
I may be cofused by the question but if I create your table in Excel, the below measure will show your results:
ID Blue = IF(VALUES(Sheet1[ID]) = 4 && VALUES(Sheet1[color]) = "blue", "YES", "no")
@dtartaglia wrote:
I may be cofused by the question but if I create your table in Excel, the below measure will show your results:
ID Blue = IF(VALUES(Sheet1[ID]) = 4 && VALUES(Sheet1[color]) = "blue", "YES", "no")
the problem with this formula is that it only works for the test data loaded. What will this formula do if there is a new row of test data with ID = 5 and color = "blue". Your formula will return no, mine will return true
Hi,
Well, basically I'm trying to do the measure without knowing which IDs are ONLY Blue. So in this case, I want the ID #4 want to be flagged, but I don't know that in advance.
I need the measure to flag #4 which is only Blue, but not #3, which has both Blue and Red rows.
Hi, try with this measure:
Does ID Have Only Blue? = IF ( HASONEVALUE ( Table1[ID] ), IF ( SELECTEDVALUE ( Table1[Color] ) = "Blue", IF ( COUNTROWS ( FILTER ( ALL ( Table1 ), Table1[ID] = SELECTEDVALUE ( Table1[ID] ) ) ) = 1, "Yes", "No" ), "No" ) )
Regards
Victor
Lima - Peru
Ah-ha! Yes, that worked - thanks...
I wrote a blog post about the process of solving this problem here https://exceleratorbi.com.au/killer-tips-write-harder-measures/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |