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

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

       

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

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

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