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

DhrumilPareshPatel — Tue, 14 Jun 2022 15:45:38 GMT

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!

topic Replicating a Spotfire case when query to a PowerBI New Column DAX Expression (switch case) in ArcGIS for Power BI Questions

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