Forum Discussion
Multiple values for the same ID
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
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.
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
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.
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.