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, re...
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"
    )
)
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?
Celador's avatar
Celador
Frequent Visitor
8 years ago

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

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

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

      • Abduvali's avatar
        Abduvali
        Skilled Sharer
        8 years ago

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

                           )