DAX - countif after grouping

[Celador]

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 3 

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'

[Abduvali]

Hi @Celador,

• 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

[Celador]

Hi @Abduvali

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

Instead of  3 & 2

[Abduvali]

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?

[Celador]

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

[Abduvali]

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

[Celador]

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

[Abduvali]

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

[Zubair_Muhammad · Accepted solution]

Hi @Celador

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

[Celador]

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.

[Zubair_Muhammad]

Hi @Celador

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

[Celador]

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

[Abduvali]

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

                   )