Forum Discussion

GTS_ONE's avatar
GTS_ONE
Icon for Advocate II rankAdvocate II
8 years ago
Solved

Can a Measure sum other rows besides itself?

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...
Need Help
  • Vvelarde's avatar
    Vvelarde
    8 years ago

    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's avatar
GTS_ONE
Icon for Advocate II rankAdvocate II
8 years ago

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's avatar
MattAllington
Icon for Community Champion rankCommunity Champion
8 years ago
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.