SQL Group By/CASE translated to DAX

[JJ_NetCo]

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.

[v-piga-msft · Accepted solution]

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]

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