intersecting streams matrix

TamerOmki — Thu, 06 Feb 2025 09:14:35 GMT

Hello dears,

within my report, I have 4 revenue streams where every user might use multiple streams during the month.

I'm studying the count of users who use each pair of streams.

within the below link, I added a sample of the raw data in addition to the expected outcome.

https://docs.google.com/spreadsheets/d/1sqeFj_VjTEtFli9hranSLtn9c0CQ2HeE-3QoURlu_UU/edit?usp=sharing

would you please help with the measure?

Thanks.

Re: intersecting streams matrix

quantumudit — Thu, 06 Feb 2025 19:40:48 GMT

Hello TamerOmki

Thank you for providing the sample data and desired outcome. I made a slight modification to the "Raw Data 2" table. Instead of having both Stream and Stream1 columns in the same table, we need to create a single table with two columns: "Stream Code" "Stream," and duplicate this table.

Here are the tables I have taken. Please note that the tables are disconnected, and the "StreamRows" and "StreamCols" tables are, containing only the two columns mentioned above.

Now, we can create the following measure to get the desired result. Here is the DAX

Unique Users = VAR _stCode1 = CALCULATE ( VALUES ( StreamRows[Stream Code] ), StreamRows[Stream] = SELECTEDVALUE ( StreamRows[Stream] ) ) VAR _stCode2 = CALCULATE ( VALUES ( StreamCols[Stream Code] ), StreamCols[Stream] = SELECTEDVALUE ( StreamCols[Stream] ) ) VAR _userCount1 = SELECTCOLUMNS ( FILTER ( Users, Users[Strem code] = _stCode1 ), Users[User] ) VAR _userCount2 = SELECTCOLUMNS ( FILTER ( Users, Users[Strem code] = _stCode2 ), Users[User] ) VAR _userCount = COUNTROWS ( INTERSECT ( _userCount1, _userCount2 ) ) RETURN _userCount

Here is the screenshot of the results:

I am also attaching the Power BI file for your reference.

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

Proud to be SuperUser

Re: intersecting streams matrix

TamerOmki — Fri, 07 Feb 2025 15:08:15 GMT

thanks a lot

topic Re: intersecting streams matrix in DAX Commands and Tips

intersecting streams matrix

Re: intersecting streams matrix

Re: intersecting streams matrix