cancel
Showing results for
Did you mean:
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.

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.

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

Proud to be a Datanaut!

10 REPLIES 10
Super User

## Re: Can a Measure sum other rows besides itself?

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

## Re: Can a Measure sum other rows besides itself?

Hi Matt,

But sorry, can you help me understand what that first "AND" is doing there?  I'm not quite sure I follow that one...

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

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.

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

Proud to be a Datanaut!

Super User

Hi @GTS_ONE,

Will this do?

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

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

## Re: Can a Measure sum other rows besides itself?

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

Announcements

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

#### Power Platform Summit North America

Register by September 5 to save \$200

#### PBI Community Highlights

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

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 50 members 1,301 guests
Recent signins: