Forum Discussion

Brendan42's avatar
Brendan42
Regular Visitor
3 years ago
Solved

Identify first instance across multiple columns

I've got a dataset where, for each row, i have to determine the first instance across a group of 5 columns where "Yes" has been entered and have that in a new column called "First Option". I've mocke...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    andhiii079845 Try this, PBIX is attached below signature.

    First Option = 
    VAR __Path = [Option 1] & "|" & [Option 2] & "|" & [Option 3] & "|" & [Option 4] & "|" & [Option 5]
    VAR __Table = 
        ADDCOLUMNS(
            GENERATESERIES(1,5,1),
            "Option", PATHITEM(__Path,[Value])
        )
    VAR __First = MINX(FILTER(__Table,[Option] = "Yes"),[Value])
    VAR __Result = IF(__First <> BLANK(), "Option " & __First, BLANK())
RETURN
    __Result
Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

andhiii079845 Try this, PBIX is attached below signature.

First Option = 
    VAR __Path = [Option 1] & "|" & [Option 2] & "|" & [Option 3] & "|" & [Option 4] & "|" & [Option 5]
    VAR __Table = 
        ADDCOLUMNS(
            GENERATESERIES(1,5,1),
            "Option", PATHITEM(__Path,[Value])
        )
    VAR __First = MINX(FILTER(__Table,[Option] = "Yes"),[Value])
    VAR __Result = IF(__First <> BLANK(), "Option " & __First, BLANK())
RETURN
    __Result