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,
Input table
Feature ID | Title Flag | Workshop Status Output |
RFID001 | 301 | 1 |
RFID001 | 301 | 1 |
RFID001 | 301 | 1 |
RFID001 | 201 | 1 |
RFID001 | 202 | 1 |
RFID001 | 203 | 1 |
RFID002 | 204 | 0 |
RFID002 | 205 | 0 |
RFID002 | 206 | 0 |
RFID003 | 202 | 0 |
RFID003 | 204 | 0 |
RFID003 | 205 | 0 |
Above is my example table. Here I want to see if for each Feature ID if there exists Title Flag 202 and 203 both, then I want my Workshop Status = 1. If there exists only 1 out of the 2 titles 202 and 203, my Workshop STatus would still be 0(See RFID003)
How can i implement this in Power BI.
I prefer this solution in M Code, but I know it gets messy there. In that case DAX is also fine.
Solved! Go to Solution.
Hi @Anonymous ,
Please follow these steps:
1.Duplicate table-->Group and Combine values:
2. Add a custom column:
3. Merge these two tables:
Or use the following formula in DAX to create a new column:
Column =
VAR _all =
SUMMARIZE (
FILTER ( 'Table', 'Table'[Feature ID] = EARLIER ( 'Table'[Feature ID] ) ),
[Title Flag]
)
RETURN
IF ( 202 IN _all && 203 IN _all, 1, 0 )
The final output is shown below:
Please take a look at the pbix file here.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Please follow these steps:
1.Duplicate table-->Group and Combine values:
2. Add a custom column:
3. Merge these two tables:
Or use the following formula in DAX to create a new column:
Column =
VAR _all =
SUMMARIZE (
FILTER ( 'Table', 'Table'[Feature ID] = EARLIER ( 'Table'[Feature ID] ) ),
[Title Flag]
)
RETURN
IF ( 202 IN _all && 203 IN _all, 1, 0 )
The final output is shown below:
Please take a look at the pbix file here.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnLzdDEwMFTSUTIGk4ZKsTrkihrhEDXCKmqMJmoEFjUBkgYYoqZYRc3QRI3htmGKoptrjGxuLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Feature ID" = _t, #"Title Flag" = _t, #"Workshop Status Output" = _t]),
#"Grouped Rows" = Table.Group(Source, {"Feature ID"}, {{"titles", each _, type table [Feature ID=nullable text, Title Flag=nullable text, Workshop Status Output=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Status", each if Table.Contains([titles],[#"Title Flag"="202"]) and Table.Contains([titles],[#"Title Flag"="203"]) then 1 else 0),
#"Expanded titles" = Table.ExpandTableColumn(#"Added Custom", "titles", {"Title Flag"}, {"Title Flag"})
in
#"Expanded titles"
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 |