Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  SQL to DAX nested case statement - Please Help!!

    Posted Jan 22, 2022 06:12 AM
    Hi All,

    I have a nested sql query that i want to convert to DAX

    select

    case when table1.cnt = 0 then (case when sales >= (select low from revenue where type = 'r1') and (case when sales <= (select up from revenue where type = 'r1') then t.range1 when sales >= (select low from revenue where type = 'r2') and (case when sales <= (select up from revenue where type = 'r2') then t.range2 else 0 end) case when table1.cnt = 1 then (case when sales >= (select low from revenue where type = 'r1') and (case when sales <= (select up from revenue where type = 'r1') then t.range1 when sales >= (select low from revenue where type = 'r2') and (case when sales <= (select up from revenue where type = 'r2') then t.range2 else 0 end) end as value from table1 left join typesrange t on t.cnt = table1.cnt

    Please help!!

    Thanks in advance.

    ------------------------------
    Tay gupta
    SDE
    ------------------------------


  • 2.  RE: SQL to DAX nested case statement - Please Help!!

    Posted Jan 24, 2022 10:25 AM

    I'm a pretty novice SQL user, so I can't provide a sample based on your code, but I'd suggest a combination of a SWITCH and TRUE statement. SWITCH is essentially a multiple IF statement - it'll go down each row and stop at the first match. The && means the criteria needs to match column values to be true.

    Here's and example of a code I used to bucket data, based on criteria from two different columns:

    act_BIN_ENG = SWITCH(TRUE(),
    sat_act_DW[act_eng] >= 30 && sat_act_DW[test_used] = 2, "1- 30-36",
    sat_act_DW[act_eng] >= 24 && sat_act_DW[test_used] = 2, "2- 24-29",
    sat_act_DW[act_eng] >= 18 && sat_act_DW[test_used] = 2, "3- 18-23",
    sat_act_DW[act_eng] >= 12 && sat_act_DW[test_used] = 2, "4- 12-17",
    sat_act_DW[act_eng] >= 6 && sat_act_DW[test_used] = 2, "5- 6-11",
    sat_act_DW[act_eng] < 6 && sat_act_DW[test_used] = 2, "6- Below 6"
    )


    ------------------------------
    Kellie Delmonico
    Statistical Assistant
    ------------------------------



  • 3.  RE: SQL to DAX nested case statement - Please Help!!

    Silver Contributor
    Posted Jan 24, 2022 10:29 AM
    Can I ask the use case for moving logic from SQL into Dax? Best practice is to move logic upstream where possible and avoid calculated columns in the model. Complex calculated columns hinder performance because the data is not actually part of the model, but lives in memory when using the report. Importing your values through SQL logic actually puts the data in the model and the report will be more performant.

    ------------------------------
    Mike Kromminga
    Data Architect
    ------------------------------