Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have the following dataset
ID | Lane |
abc100 | Lane 2 |
abc100 | Lane 3 |
abc200 | Lane 2 |
abc300 | Lane 3 |
abc400 | Lane 2 |
abc400 | Lane 3 |
Based on this dataset I want to create 2 flag columns called Lane 2 flag and Lane 3 flag.
If for a particular ID there exists a Lane 2 then all rows for Lane 2 flag should become Yes else all rows for Lane 2 flag (for that ID) should become No. The same calculation for Lane 3 flag.
Please find below the expected output.
*Rows are color-coded based on ID groups for understanding only
ID | Lane | Lane 2 flag | Lane 3 flag |
abc100 | Lane 2 | Yes | Yes |
abc100 | Lane 3 | Yes | Yes |
abc200 | Lane 2 | Yes | No |
abc300 | Lane 3 | No | Yes |
abc400 | Lane 2 | Yes | Yes |
abc400 | Lane 3 | Yes | Yes |
abc500 | Lane 1 | No | No |
Solved! Go to Solution.
Using a DAX calculated column, here's what I came up with for Lane 2 Flag:
Lane 2 Flag = IF( COUNTROWS( FILTER( ALL(TableName), TableName[ID] = EARLIER(TableName[ID]) && TableName[Lane] = "Lane 2" ) ) > 0, "Yes", "No" )
If that works for you, I'm sure coming up with Lane 3 Flag won't be too difficult from there...
Proud to be a Super User!
Using a DAX calculated column, here's what I came up with for Lane 2 Flag:
Lane 2 Flag = IF( COUNTROWS( FILTER( ALL(TableName), TableName[ID] = EARLIER(TableName[ID]) && TableName[Lane] = "Lane 2" ) ) > 0, "Yes", "No" )
If that works for you, I'm sure coming up with Lane 3 Flag won't be too difficult from there...
Proud to be a Super User!
Thanks, This works
Could you also explain the logic as I'm not an advanced user.
Absolutely. The ALL function returns the entire table, which is then filtered down to only rows that match the filtering criteria in the next two lines. EARLIER means on each row, check that the ID column in the filtered ALL table matches the current row's ID. So in more plain English this formula says:
"On each row in this table, count all rows in the entire table where the ID is the same as this row, and the Lane = 'Lane 2'. If that count is greater than 0, then there is something in Lane 2, so mark this row as 'Yes'. Otherwise, mark it 'No'."
Proud to be a Super User!
Wow, thanks again !
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |