intersecting streams matrix

[TamerOmki]

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.

[TamerOmki]

thanks a lot

[quantumudit · Accepted solution]

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