Forum Discussion

TamerOmki's avatar
TamerOmki
Frequent Visitor
1 year ago
Solved

intersecting streams matrix

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.

  • quantumudit's avatar
    quantumudit
    1 year ago

    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

2 Replies

  • quantumudit's avatar
    quantumudit
    Super User
    1 year ago

    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