cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## DAX - countif after grouping

Hi,

I need a series of measures to group some results by two columns and then count specific results in the 3rd column - and I'm very very new to Power BI and DAX.

I've done a bunch of googling, read through a number of posts on here, but I just can't seem to amend the measures I've seen to fit my scenario

A mock up of some data is in the image below:

Measure #1 would need to count the number of 'Red' Results based on distinct UserId's / ResponseId's

Measure #1 would need to count the number of 'Blue' Results based on distinct UserId's / ResponseId's

Using the example data below:

The result of Measure #1 would be

The result of Measure #2 would be 2

Or in more visual turns, this is effectively what the measure would be doing (see image below) - grouping by User and Response - followed by a count of 'Red' or 'Blue'

1 ACCEPTED SOLUTION
Community Champion

Try these MEASURES as well

```Red =
COUNTROWS (
FILTER (
SUMMARIZE ( Table1, Table1[UserId], Table1[ResponseId], Table1[Result] ),
Table1[Result] = "Red"
)
)```

And

```Blue =
COUNTROWS (
FILTER (
SUMMARIZE ( Table1, Table1[UserId], Table1[ResponseId], Table1[Result] ),
Table1[Result] = "Blue"
)
)```

Regards
Zubair

12 REPLIES 12
Skilled Sharer

• Measure #1 would equate to 3: Red = Calculate(count('your table name'[Result]),'your table name'[Result] = "Red")
• Measure #2 would equate to 2: Blue = Calculate(count('your table name'[Result]),'your table name'[Result] = "Blue")

Regards

Abduvali

Frequent Visitor

Apologies, I initially marked this as the solution, but it doesn't appear to resolve the issue of grouping by UserId and ResponseId before performing the Count. I was a little presumptious because the measure actually produced results, where all my other attempts have so far just generated errors! 🙂 🙂

Your solution looks like it would generate the following:

Measure #1 -  7

Measure #2 -  6

Skilled Sharer

Yes it will give you 6 and 7 unless you will group them by UserID once grouped it will give you 2 and 3

• Do you know how to use grouping in Power BI?
Frequent Visitor

@Abduvali Not really, I have a rough understanding of the SUMMARIZE function - which is what I thought I would need, but not enough to generate the answer I'm after.

Sorry if i wasn't clear in the original post, it's the combination of grouping by those columns, followed by the count that I'm struggling with.

Skilled Sharer

See below on how to group your table to get to desired table look:

Group By function in Edit Query Mode

Final Result

Hope this helps.

Regards

Abduvali

Frequent Visitor

Is there no way to write a single measure that will result in the Answer of 3 & 2?

Skilled Sharer

Create new column with IF statement in it:

Column =

if(Sheet2[Level] = "red",

CALCULATE(COUNT(Sheet2[level]),Sheet2[Level] = "Red"),

CALCULATE(COUNT(Sheet2[level]),Sheet2[Level] = "Blue")

)

Regards

Abduvali

Community Champion

Try these MEASURES as well

```Red =
COUNTROWS (
FILTER (
SUMMARIZE ( Table1, Table1[UserId], Table1[ResponseId], Table1[Result] ),
Table1[Result] = "Red"
)
)```

And

```Blue =
COUNTROWS (
FILTER (
SUMMARIZE ( Table1, Table1[UserId], Table1[ResponseId], Table1[Result] ),
Table1[Result] = "Blue"
)
)```

Regards
Zubair

Frequent Visitor

Hi @Zubair_Muhammad - this is what I was after, I've put it in place and it is working as expected - thank you kindly.

@Abduvali - really appreciate your time & you did show me a few things, it just wasn't quite what I was after, but I do appreciate it.

Community Champion

Just for knowledge sharing. Another way of doing this

```Blue =
COUNTROWS (
FILTER (
ALL ( Table1[UserId], Table1[ResponseId], Table1[Result] ),
Table1[Result] = "Blue"
)
)```

Similar way for Red

Regards
Zubair

Frequent Visitor

Thanks @Zubair_Muhammad - is there any significant performance cost difference between the two solutions?

Skilled Sharer

You IF statement, create new column:

Column = if(Sheet2[Level] = "red",

CALCULATE(COUNT(Sheet2[level]),Sheet2[Level] = "Red"),

CALCULATE(COUNT(Sheet2[level]),Sheet2[Level] = "Blue")

)

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors