For Each/Exists type of calculation in DAX

[Anonymous]

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.

[v-eqin-msft · Accepted 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.

[lbendlin]

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"