Multiple values for the same ID

[japolo]

Hi,

I'm have a fact table where the ID is shown multiple times since the ID can have multiple events. I want to summarize the number of IDs that have had e.g. add_to_cart and add_to_wishlist. How should I go by with a measure, alternatively a custom column? I'm not sure about the DAX code needed to be able to do it. Something like if a session has add_to_cart and add_to_wishlist, return 1, else 0.

Kind regards,

J

[parry2k · Accepted solution]

@japolo you can add a measure to get the ids that meets the condition

Count Measure = 
VAR __addtocartTable = CALCULATETABLE ( VALUES ( Table[ID] ), Table[Event] = "add_to_cart" )
VAR __addtowishTable = CALCULATETABLE ( VALUES ( Table[ID] ), Table[Event] = "add_to_wishlist" )
RETURN
COUNTROWS ( INTERSECT  ( __addtocartTable, __addtowishTable ) )

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[Anonymous · Accepted solution]

Hi @japolo ,

Sample:

I suggest you to create a virtual table in your measure.

Measure = 
VAR _SUMMAIRZE  = SUMMARIZE('Table',[session_id],"Flag",VAR _EVENT_LIST = CALCULATETABLE(VALUES('Table'[event]),ALLEXCEPT('Table','Table'[session_id]))
Return
IF(AND( "add_to_cart" IN _EVENT_LIST ,"add_to_wishlist" IN _EVENT_LIST),1,0))
RETURN
COUNTAX(FILTER(_SUMMAIRZE,[Flag] = 1),[session_id])

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.

[parry2k · Accepted solution]

@japolo you can add a measure to get the ids that meets the condition

Count Measure = 
VAR __addtocartTable = CALCULATETABLE ( VALUES ( Table[ID] ), Table[Event] = "add_to_cart" )
VAR __addtowishTable = CALCULATETABLE ( VALUES ( Table[ID] ), Table[Event] = "add_to_wishlist" )
RETURN
COUNTROWS ( INTERSECT  ( __addtocartTable, __addtowishTable ) )

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.