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
Highlighted
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!

3 REPLIES 3
Highlighted
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!

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 

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
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 354 members 3,562 guests
Please welcome our newest community members: