Can a Measure sum other rows besides itself?

[GTS_ONE]

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!

[Ashish_Mathur]

Hi @GTS_ONE,

Will this do?

[MattAllington]

How about this

Measure =
CALCULATE (
    AND ( CONTAINS ( 'Table', 'Table'[Colour], "Blue" ), COUNTROWS ( 'Table' ) = 1 ),
    FILTER ( ALL ( 'Table' ), 'Table'[ID] = SELECTEDVALUE ( 'Table'[ID] ) )
)

[GTS_ONE]

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...

[MattAllington]

---

@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.  

[dtartaglia]

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")

[MattAllington]

---

@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

[GTS_ONE]

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.

[Vvelarde · Accepted solution]

@GTS_ONE

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

[GTS_ONE]

Ah-ha!  Yes, that worked - thanks...

[MattAllington]

I wrote a blog post about the process of solving this problem here https://exceleratorbi.com.au/killer-tips-write-harder-measures/