Identify first instance across multiple columns

[Brendan42]

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 mocked it up to illustrate in Excel (pictured below).

1) Column G ("First Option") should be calculated to identify the first column, moving from left-to-right, where "Yes" has been entered.

2) The data is pulled from another system, so i cannot change that, have to do this in Power BI

3) I can't count total "Yes" answers and allocate accordingly (eg. 4 x "Yes" = Option 2, 3 x "Yes" = Option 3) because a "No" is still possible after a "Yes" as you move along the columns.

Any idea please.

[Greg_Deckler · Accepted solution]

@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

[Brendan42]

Thank you @Greg_Deckler , works perfectly

[andhiii079845]

Can you show please a data example of the underlaying table(s)?