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

     

dtartaglia's avatar
dtartaglia
Icon for Resolver I rankResolver I
8 years ago

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

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

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's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    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