Forum Discussion

OneData's avatar
OneData
New Member
3 years ago
Solved

Power BI version of 'Case When' using "Switch" Function

Hi guys - i'm new to dax and am having a bit oftrouble with what I would like to think should be something simple.

 

Essentially i have paramaters for each bucket listed below and I want those to dynammically fall into one column. I think I've done most of the hard part but I can't seem to put the right measure together. - Is this possible with a measure? I don't think i can add a numeric parameter in the calucalted column in power query so i'm kinda stuck here. Hopefully this is as simple for one of you as it would be for me in sql.

 

What am i doing wrong here?

 

ENLR Delta =
SWITCH(
    TRUE(),
    FILTER('Application','Application'[Fico Range] = "640-679"),([Net Loss Ratio] - [640-679 Value]),
    FILTER('Application]','Application'[Fico Range] = "680-699"),([Net Loss Ratio] - [680-699 Value]),
    FILTER('Application','Application'[Fico Range] = "700-739"),([Net Loss Ratio] - [700-739 Value]),
    FILTER('Application','Application'[Fico Range] = "740-799"),([Net Loss Ratio] - [740-799 Value]),
    FILTER('Application','Application'[Fico Range] = "800+"),([Net Loss Ratio] - [800+ Value]),
    0
)
DAX
DAX SWITCH
SQL
  • OneData's avatar
    OneData
    3 years ago

    Fixed it - looks like instead of filter - using contains worked just fine for me - hope this helps someone in the future!

     

    ENLR Delta =
    SWITCH(
        TRUE(),
        CONTAINS('Application','Application'[Fico Range] = "640-679"),([Net Loss Ratio] - [640-679 Value]),
        CONTAINS('Application]','Application'[Fico Range] = "680-699"),([Net Loss Ratio] - [680-699 Value]),
        CONTAINS('Application','Application'[Fico Range] = "700-739"),([Net Loss Ratio] - [700-739 Value]),
        CONTAINS('Application','Application'[Fico Range] = "740-799"),([Net Loss Ratio] - [740-799 Value]),
        CONTAINS('Application','Application'[Fico Range] = "800+"),([Net Loss Ratio] - [800+ Value]),
        0
    )

3 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi OneData 

    what contexts do you have for the measure? Or how are you going to present this measure?

    • OneData's avatar
      OneData
      New Member
      3 years ago

      Similarly to case when - it will be presented in a single column - however for each bucket i need each desired input. I was able to accomplish this in power query during etl on a another column in the dataset but this one here depends on numeric parameters so i have to figure out a measure to accomplish the "case when" scenario

       

  • OneData's avatar
    OneData
    New Member
    3 years ago

    Fixed it - looks like instead of filter - using contains worked just fine for me - hope this helps someone in the future!

     

    ENLR Delta =
    SWITCH(
        TRUE(),
        CONTAINS('Application','Application'[Fico Range] = "640-679"),([Net Loss Ratio] - [640-679 Value]),
        CONTAINS('Application]','Application'[Fico Range] = "680-699"),([Net Loss Ratio] - [680-699 Value]),
        CONTAINS('Application','Application'[Fico Range] = "700-739"),([Net Loss Ratio] - [700-739 Value]),
        CONTAINS('Application','Application'[Fico Range] = "740-799"),([Net Loss Ratio] - [740-799 Value]),
        CONTAINS('Application','Application'[Fico Range] = "800+"),([Net Loss Ratio] - [800+ Value]),
        0
    )