Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
i have a table with columns
ID TXT102 TXT345 TXT567 NewColumn
1 No yes No yes
2 yes No yes yes
3 No No No No
every month the after TXT the numbers changes.Want a conditional new column has above if any coumn contains (txt) = "yes" display yes else no.
Hi @sanjay009 ,
According to my understand, the column names are constantly changing except the ID column ,right?
You could follow these steps or take a look at my pbix file here.
1. Select the ID column and click Transpose ,then "Use first row as headers".
2. Add a Index column (used as Matrix rows )
3. Use the formula:
ID is 1 =
VAR _flag =
IF ( MAX ( 'Table2'[1] ) = "yes", 1, 0 )
VAR _total =
IF ( _flag > 0, "yes", "No" )
RETURN
IF ( HASONEVALUE ( Table2[Index] ), MAX ( 'Table2'[1] ), _total )
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
Hi,
Will there always be only 3 TXT column? Also, as Amit has suggested, are you agreeable to using the "Unpivot other columns" feature?
@sanjay009 , I think the best way select ID and use unpivot other columns .
Then you will have a column like Txt and Value( Yes/No)
Then you can have a new column
if(countx(filter(Table, [Id] = earlier([id]) && [TXT] ="yes" ), [Id])+0 >0, "Yes","No")
You can use that in Matrix now
@sanjay009 Columns changing names is not a good thing at all. Is there any way you can not promote headers on import?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |