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.
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
* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.