Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only

View My Drafts

  • 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
    ------------------------------

    Original Message


  • 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
    ------------------------------

    Original Message