Forum Discussion

Celador's avatar
Celador
Frequent Visitor
8 years ago
Solved

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

 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

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

12 Replies

  • Abduvali's avatar
    Abduvali
    Skilled Sharer
    8 years ago

    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's avatar
      Celador
      Frequent Visitor
      8 years ago

      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's avatar
        Abduvali
        Skilled Sharer
        8 years ago

        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?