cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GTS_ONE Regular Visitor
Regular Visitor

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

 

3.PNG

 

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.

 

5.PNG

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Vvelarde Super Contributor
Super Contributor

Re: Can a Measure sum other rows besides itself?

@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

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




10 REPLIES 10
Super User
Super User

Re: Can a Measure sum other rows besides itself?

How about this

 

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


* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
GTS_ONE Regular Visitor
Regular Visitor

Re: Can a Measure sum other rows besides itself?

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

Re: Can a Measure sum other rows besides itself?

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 Regular Visitor
Regular Visitor

Re: Can a Measure sum other rows besides itself?

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 Super Contributor
Super Contributor

Re: Can a Measure sum other rows besides itself?

@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

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: Can a Measure sum other rows besides itself?

Hi @GTS_ONE,

 

Will this do?

 

Untitled.png

Super User
Super User

Re: Can a Measure sum other rows besides itself?


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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Super User
Super User

Re: Can a Measure sum other rows besides itself?


@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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
GTS_ONE Regular Visitor
Regular Visitor

Re: Can a Measure sum other rows besides itself?

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 50 members 1,301 guests
Please welcome our newest community members: