Replicating a Spotfire case when query to a PowerBI New Column DAX Expression (switch case)

229
0
06-14-2022 08:14 AM
DhrumilPareshPatel
New Contributor

I am migrating a Spotfire Report to a PowerBI Report. For one of the columns, I have a Spotfire case when query that I want to convert to a switch case DAX Expression. I started out and I think I am good with the initial part of the query but am not sure how to build the rest of the query. Here are the details:

Spotfire case when query:

CASE  WHEN (

 [RefPointGroupCap] is not null) OR (

 [MortgageGroupCap] is not null) OR (

 [MPBGroupCap] is not null) OR (

 [NCBGroupCap] is not null) OR (

 [SRCGroupCap] is not null) THEN

     "Y"

        & (if([RefPointGroupCap] is null,""," / " & [RefPointGroupCap]))

        & (If([MortgageGroupCap] is null,""," / " & [MortgageGroupCap]))

        & (If([MPBGroupCap] is null,""," / " & [MPBGroupCap]))

        & (If([NCBGroupCap] is null,""," / " & [NCBGroupCap]))

        & (If([SRCGroupCap] is null,""," / " & [SRCGroupCap]))

WHEN (

 [RefPointGroupCap] is null) and (

 [MortgageGroupCap] is null) and (

 [MPBGroupCap] is null) and (

 [NCBGroupCap] is null) and (

 [SRCGroupCap] is null) THEN "N"

END

The switch case query I built half way:

Column=

SWITCH (

TRUE (),

NOT(ISBLANK('F22_New_Cumulative_export (2)'[RefPointGroupCap])) ||

NOT(ISBLANK('F22_New_Cumulative_export (2)'[MortgageGroupCap])) ||

NOT(ISBLANK('F22_New_Cumulative_export (2)'[MPBGroupCap])) ||

NOT(ISBLANK('F22_New_Cumulative_export (2)'[NCBGroupCap])) ||

NOT(ISBLANK('F22_New_Cumulative_export (2)'[SRCGroupCap])) ,

"Y" &&

(IF(ISBLANK('F22_New_Cumulative_export (2)'[RefPointGroupCap]), BLANK(), " / " & 'F22_New_Cumulative_export (2)'[RefPointGroupCap]) &&

(IF(ISBLANK('F22_New_Cumulative_export (2)'[MortgageGroupCap]), BLANK(), " / " & 'F22_New_Cumulative_export (2)'[MortgageGroupCap]) &&

(IF(ISBLANK('F22_New_Cumulative_export (2)'[MPBGroupCap]), BLANK(), " / " & 'F22_New_Cumulative_export (2)'[MPBGroupCap]) &&

(IF(ISBLANK('F22_New_Cumulative_export (2)'[NCBGroupCap]), BLANK(), " / " & 'F22_New_Cumulative_export (2)'[NCBGroupCap]) &&

(IF(ISBLANK('F22_New_Cumulative_export (2)'[SRCGroupCap]), BLANK(), " / " & 'F22_New_Cumulative_export (2)'[SRCGroupCap]),

ISBLANK('F22_New_Cumulative_export (2)'[RefPointGroupCap]) ||

ISBLANK('F22_New_Cumulative_export (2)'[MortgageGroupCap]) ||

ISBLANK('F22_New_Cumulative_export (2)'[MPBGroupCap]) ||

ISBLANK('F22_New_Cumulative_export (2)'[NCBGroupCap]) ||

ISBLANK('F22_New_Cumulative_export (2)'[SRCGroupCap]),

"N"

))))))

Let me know if you need any more information, thanks for the help!

0 Kudos
0 Replies