CODEWITHSUNDEEP
if-statementpowerbidax
zinski212
zinski212

Reputation: 11

DAX Formula for Multiple Rows

I have created a DAX formula in which would provide me the cause for every Yes response. However, I noticed there was a potential to have multiple 'Yes' responses per row.

For example: Screenshot

How can I have it where it pulls all the 'Yes' Responses, adds them in the Cause Column, and then parses the valves?

Cause = 
IF(All_HAPI_Data[Cause_Edema]="Yes","Edema",
  (IF(All_HAPI_Data[Cause_Equipment]="Yes", "Equipment",
     (IF(All_HAPI_Data[Cause_Not_Propped]="Yes", "Not Propped",
        (IF(All_HAPI_Data[Cause_Not_Turned]="Yes", "Not Turned",
           (IF(All_HAPI_Data[Cause_Other]="Yes", "Other",
              (IF(All_HAPI_Data[Cause_MASD_DD_IAD_ITD]="Yes", "MASD DD/IAD/ITD",
                 (IF(All_HAPI_Data[Cause_MASD_Erosion]="Yes", "MASD Erosion",
                    (IF(All_HAPI_Data[Cause_MARSI]="Yes", "MARSI",
                       (IF(All_HAPI_Data[Cause_Unstable]="Yes", "Unstable", "None Selected")))))))))))))))))

Upvotes: 1

Views: 473

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

For a single value, I'd recommend using a SWITCH instead of a bunch of nested IF functions.

Cause =
SWITCH (
    TRUE (),
    All_HAPI_Data[Cause_Edema]           = "Yes", "Edema",
    All_HAPI_Data[Cause_Equipment]       = "Yes", "Equipment",
    All_HAPI_Data[Cause_Not_Propped]     = "Yes", "Not Propped",
    All_HAPI_Data[Cause_Not_Turned]      = "Yes", "Not Turned",
    All_HAPI_Data[Cause_Other]           = "Yes", "Other",
    All_HAPI_Data[Cause_MASD_DD_IAD_ITD] = "Yes", "MASD DD/IAD/ITD",
    All_HAPI_Data[Cause_MASD_Erosion]    = "Yes", "MASD Erosion",
    All_HAPI_Data[Cause_MARSI]           = "Yes", "MARSI",
    All_HAPI_Data[Cause_Unstable]        = "Yes", "Unstable",
    "None Selected"
)

However, if you need to concatenate them, you can do this:

Cause =
VAR CauseList =
    {
        IF ( All_HAPI_Data[Cause_Edema] = "Yes", "Edema" ),
        IF ( All_HAPI_Data[Cause_Equipment] = "Yes", "Equipment" ),
        IF ( All_HAPI_Data[Cause_Not_Propped] = "Yes", "Not Propped" ),
        IF ( All_HAPI_Data[Cause_Not_Turned] = "Yes", "Not Turned" ),
        IF ( All_HAPI_Data[Cause_Other] = "Yes", "Other" ),
        IF ( All_HAPI_Data[Cause_MASD_DD_IAD_ITD] = "Yes", "MASD DD/IAD/ITD" ),
        IF ( All_HAPI_Data[Cause_MASD_Erosion] = "Yes", "MASD Erosion" ),
        IF ( All_HAPI_Data[Cause_MARSI] = "Yes", "MARSI" ),
        IF ( All_HAPI_Data[Cause_Unstable] = "Yes", "Unstable" )
    }
VAR RemoveBlanks = FILTER ( CauseList, NOT ISBLANK ( [Value] ) )
RETURN
    IF (
        ISEMPTY ( RemoveBlanks ), "None Selected",
        CONCATENATEX ( RemoveBlanks, [Value], ", " )
    )

Side Note: In general, it's easier to work with unpivoted data in DAX. You could transform a table like

ID Cause1 Cause2 Cause3
1 Yes No No
2 No Yes Yes

Into

ID Cause YesNo
1 Cause1 Yes
1 Cause2 No
1 Cuase3 No
2 Cause1 No
2 Cause2 Yes
2 Cuase3 Yes

or more succinctly by filtering out No values entirely

ID Cause
1 Cause1
2 Cause2
2 Cause3

Either of these last two formats are much easier to write measures with.

Upvotes: 1

Related Questions