Forum Discussion

JJ_NetCo's avatar
JJ_NetCo
Frequent Visitor
7 years ago
Solved

SQL Group By/CASE translated to DAX

 

Hi there. I'm new to PowerBi and especially DAX. Need some help to transform some SQL syntax to DAX.

I'll try a simplifyed example(my data has several weeks and teams). If I have data like this:

 

 

I want to group by Calendarweek, Team, and the calculate the part/percentage of all rows. So NPS_Type = 3 will be 9/15 = 60,0%, and NPS_Type 1 will be 1/15 = 6,7%. The SQL calculation for this is : 

SELECT Calendarweek, Team,

(SUM(CASE WHEN [NPS_type] = 3 THEN numberOfRows ELSE 0 END)*1.0/SUM(numberOfRows) * 100) AS PromotorsPrc,

(SUM(CASE WHEN [NPS_type] = 1 THEN numberOfRows ELSE 0 END)*1.0/SUM(numberOfRows) * 100) AS DetractorsPrc

FROM [MyTable]

GROUP BY Calendarweek, Team

How to do this in DAX? I've tried with SUMMARIZE and GROUP BY, but get lost when I try to implement the CASE. Tried SWITCH and IF, can't get the syntax right.

Any suggestions appreciated, thanks.

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi JJ_NetCo ,

    By my tests and research, you could create the two calcualted columns to achieve your output.

    PromotorsPrc =
IF ( 'Table_7'[NPS_Type] = 3, 'Table_7'[numberOfRows], 0 ) * 1
    / CALCULATE (
        SUM ( Table_7[numberOfRows] ),
        ALLEXCEPT ( Table_7, 'Table_7'[Calendarweek], Table_7[Team] )
    ) * 100
DetractorsPrc =
IF ( 'Table_7'[NPS_Type] = 1, 'Table_7'[numberOfRows], 0 ) * 1
    / CALCULATE (
        SUM ( Table_7[numberOfRows] ),
        ALLEXCEPT ( Table_7, 'Table_7'[Calendarweek], Table_7[Team] )
    ) * 100

    Then you could get the output below.

    Hope this can help you!

    Best  Regards,

    Cherry

     

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi JJ_NetCo ,

    By my tests and research, you could create the two calcualted columns to achieve your output.

    PromotorsPrc =
IF ( 'Table_7'[NPS_Type] = 3, 'Table_7'[numberOfRows], 0 ) * 1
    / CALCULATE (
        SUM ( Table_7[numberOfRows] ),
        ALLEXCEPT ( Table_7, 'Table_7'[Calendarweek], Table_7[Team] )
    ) * 100
DetractorsPrc =
IF ( 'Table_7'[NPS_Type] = 1, 'Table_7'[numberOfRows], 0 ) * 1
    / CALCULATE (
        SUM ( Table_7[numberOfRows] ),
        ALLEXCEPT ( Table_7, 'Table_7'[Calendarweek], Table_7[Team] )
    ) * 100

    Then you could get the output below.

    Hope this can help you!

    Best  Regards,

    Cherry

     

    • JJ_NetCo's avatar
      JJ_NetCo
      Frequent Visitor
      7 years ago

      Thank you. Smart solution. That was exactly what I needed.