cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tomo2709 Frequent Visitor
Frequent Visitor

Nested switch statements?

Hi 

I have two conditional columns [FROMDASHBOARDSTATUS] AND [FROMOFFSHORESTATUS]

 

I am trying to apply some logic to different scenarios depending upon the result in either [FROMDASHBOARDSTATUS] or[FROMOFFSHORESTATUS].  The DAX formulae i have is:

 

FINALSTATUS = SWITCH(
       TRUE(),
       'PFUpdates'[FROMDASHBOARDSTATUS]="Approved - Modified",'Panel Firm Updates'[FROMOFFSHORESTATUS],
       'PFUpdates'[FROMDASHBOARDSTATUS]="Approved - Paid in Full",'Panel Firm Updates'[FROMOFFSHORESTATUS],
       'PFUpdates'[FROMDASHBOARDSTATUS]="Unapproved","Awaiting approval in Dashboard",
       'PFUpdates'[FROMDASHBOARDSTATUS]="Disputed","Disputed in Dashboard",
       'PFUpdates'[FROMDASHBOARDSTATUS]="Rejected","Rejected in Dashboard",
       'PFUpdates'[FROMDASHBOARDSTATUS]="Reversal","Reversal in Dashboard",
       'PFUpdates'[FROMDASHBOARDSTATUS]=BLANK(),"No Record",
       'PFUpdates'[FROMDASHBOARDSTATUS]="Unknown",'Panel Firm Updates'[FROMOFFSHORESTATUS])

 

The above works ok apart from one niggling issue (ill explain below) also, i would like to  add one more statement:

'PFUpdates'[FROMOFFSHORESTATUS]="Unknown","Unknown" but i beleive i cant add this because the starting value ( 'PFUpdates'[FROMDASHBOARDSTATUS]) is a different conditional column to the rest.  I thought a nested switch statement would help to add this additional value but i dont know if that is possible?  If anyone can advise that would be great.  

 

Regarding the niggling issue..... the statement above " 'PFUpdates'[FROMDASHBOARDSTATUS]="Approved - Paid in Full",'PFUpdates'[FROMOFFSHORESTATUS]," is basically stating if  'PFUpdates'[FROMDASHBOARDSTATUS] has a value "Approved - Paid in Full" then use the corresponding entry result found in 'PFUpdates'[FROMOFFSHORESTATUS] but... my question is if  'PFUpdates'[FROMOFFSHORESTATUS] is BLANK() then i would like the result to be "Transferred to EDM".  Am i able to modify the statement to also look for the blank values in 'PFUpdates'[FROMOFFSHORESTATUS] after it has looked for "Approved - Paid in Full" in 'PFUpdates'[FROMDASHBOARDSTATUS].

 

Any help greatly appreciated and i hope this is not too confusing?

 

Paul 

 

InkedBI SNAPHOT_LI.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
Stachu Super Contributor
Super Contributor

Re: Nested switch statements?

I think adding IF would be sufficient, also I'm not sure about order of conditions for the Unknown - is it as you expected?

FINALSTATUS =
IF (
    'PFUpdates'[FROMOFFSHORESTATUS] = "Unknown",
    "Unknown",
    SWITCH (
        'PFUpdates'[FROMDASHBOARDSTATUS],
        "Approved - Modified", 'Panel Firm Updates'[FROMOFFSHORESTATUS],
        "Approved - Paid in Full", IF (
            ISBLANK ( 'Panel Firm Updates'[FROMOFFSHORESTATUS] ),
            "Transferred to EDM",
            'Panel Firm Updates'[FROMOFFSHORESTATUS]
        ),
        "Unapproved", "Awaiting approval in Dashboard",
        "Disputed", "Disputed in Dashboard",
        "Rejected", "Rejected in Dashboard",
        "Reversal", "Reversal in Dashboard",
        BLANK (), "No Record",
        "Unknown", 'Panel Firm Updates'[FROMOFFSHORESTATUS]
    )
)
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
Stachu Super Contributor
Super Contributor

Re: Nested switch statements?

I think adding IF would be sufficient, also I'm not sure about order of conditions for the Unknown - is it as you expected?

FINALSTATUS =
IF (
    'PFUpdates'[FROMOFFSHORESTATUS] = "Unknown",
    "Unknown",
    SWITCH (
        'PFUpdates'[FROMDASHBOARDSTATUS],
        "Approved - Modified", 'Panel Firm Updates'[FROMOFFSHORESTATUS],
        "Approved - Paid in Full", IF (
            ISBLANK ( 'Panel Firm Updates'[FROMOFFSHORESTATUS] ),
            "Transferred to EDM",
            'Panel Firm Updates'[FROMOFFSHORESTATUS]
        ),
        "Unapproved", "Awaiting approval in Dashboard",
        "Disputed", "Disputed in Dashboard",
        "Rejected", "Rejected in Dashboard",
        "Reversal", "Reversal in Dashboard",
        BLANK (), "No Record",
        "Unknown", 'Panel Firm Updates'[FROMOFFSHORESTATUS]
    )
)
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Tomo2709 Frequent Visitor
Frequent Visitor

Re: Nested switch statements?

Hey Stachu - great to hear from you again.  Ill give your solution a go - will get back to you in a tick Smiley Happy

 

Paul 

Highlighted
Tomo2709 Frequent Visitor
Frequent Visitor

Re: Nested switch statements?

Hi

 

I know i have said it before in other posts but ill say it again - you are a star.  Thank you so much.  I never thought about an if statement Smiley Happy !

 

Paul 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 207 members 2,253 guests
Please welcome our newest community members: