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

                         )

    • Abduvali's avatar
      Abduvali
      Skilled Sharer
      8 years ago

      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's avatar
        Zubair_Muhammad
        Community Champion
        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"
    )
)