Forum Discussion
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 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.
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
3 Replies
Can you show please a data example of the underlaying table(s)?
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 __ResultThank you Greg_Deckler , works perfectly
