Flag events that used certain products

[tyrhoku]

I have a dataset that stores "events". Each event can have multiple records with the same ID. I want to be able to flag on that specific event ID when product A or product B were used in idividual columns.

Product A Flag = CALCULATE(
DISTINCTCOUNT('M1 Revenue Attributes & Measures'[Event ID]),
FILTER('D1 Product','[Product Type] = "A"))

Currently I can get an output like this

Region	Event ID	Product Type	Product A Flag	Product B Flag
US	123A	A	1
US	123A	B		1

End state I want to get to a table where I can show for each Event ID a flag for one A was used and when B was used. Should be a distinct count of that

Region	Event ID	Product A Flag	Product B Flag	Total
US	123A	1	1	2

[Anonymous · Accepted solution]

Hi @tyrhoku ,

Is [Product A Flag] a measure and the output above a table visual? I think you can try to remove [Product Type] column in table visual and check the result. Due to I don't know your data model, I create a sample to have a test.

D1 Product:

M1 Revenue Attributes & Measures:

Relationship:

Measures:

Product A Flag =
CALCULATE (
    DISTINCTCOUNT ( 'M1 Revenue Attributes & Measures'[Event ID] ),
    FILTER ( 'D1 Product', [Product Type] = "A" )
)

Product B Flag =
CALCULATE (
    DISTINCTCOUNT ( 'M1 Revenue Attributes & Measures'[Event ID] ),
    FILTER ( 'D1 Product', [Product Type] = "B" )
)

Total = [Product A Flag] + [Product B Flag] 

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@tyrhoku , try like this and check

Product A Flag = CALCULATE(
DISTINCTCOUNT('M1 Revenue Attributes & Measures'[Event ID]),
FILTER(all('D1 Product'[Product Type]),[Product Type] = "A"))