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

     

MattAllington's avatar
MattAllington
Icon for Community Champion rankCommunity Champion
8 years ago

How about this

 

Measure =
CALCULATE (
    AND ( CONTAINS ( 'Table', 'Table'[Colour], "Blue" ), COUNTROWS ( 'Table' ) = 1 ),
    FILTER ( ALL ( 'Table' ), 'Table'[ID] = SELECTEDVALUE ( 'Table'[ID] ) )
)
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

         

    • 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

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