Forum Discussion

Neko12's avatar
Neko12
Regular Visitor
4 years ago

IF ELSE vs SWITCH for multiple years

I am trying to do a ratio-based calculation using DAX in Power BI.

 

Say, the column name is [2023 Cost],[2022 Cost],[2021 Cost], [YYYY Cost] and there is a fixed value: [Target Cost]

 

What is the appropiate DAX/ formula for this condition(s):

If there is a value (non-blank) in [2023 Cost], it will calculate [2022 Cost]/[Target Cost];

If not (meaning [2023 Cost] is blank), it will take the Y+1 value -> [2022 Cost]/[Target Cost];

If not (meaning [2022 Cost] is blank), it will take the Y+2 value -> [2021 Cost]/[Target Cost];

so forth until [YYYY Cost],

if else (no value is recorded), it will display 0]

 

Should I use nested IF and/or SWITCH functions?


Thanks

Need Help
Tutorial Requests

3 Replies

  • Neko12's avatar
    Neko12
    Regular Visitor
    4 years ago

    Thanks amitchandak for the info.

    However, the value is not calculated - it shows text as results (eg: 'Group 1', 'Group 2')


    I'm looking for a DAX expression.

    For example:

    CostRatio = (Is it IF/ SWITCH) ........ [2023 Cost] is not blank, then the result is [2023 Cost]/[Target Cost],

    if not, then [2022 Cost]/[Target Cost],

    if not then 0

     

    Thanks

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      4 years ago

      Neko12 , something like this

       


      Switch( true() ,
      not(isblank([2023 Cost])), divide([2023 Cost],[Target Cost]),
      not(isblank([2022 Cost])) divide([2022 Cost],[Target Cost]),
      not(isblank([2021 Cost])) divide([2021 Cost],[Target Cost]) )