Forum Discussion
Matrix
Hello,
I have raw data as below:
- table 1:
- user ID
- month
- stream code: (1,2,34)
whereas, within this table each user ID might be repeated within the same month based on the stream code he used during the month.
- table 2:
- Stream code
- stream name
- stream name1 (same as stream name)
I'm trying to use a matrix visual with Stream name in the rows and stream name1 in the columns and the value should be distinct count of users who use every two streams.
can you please help with the measure?
thank you
6 Replies
Hi TamerOmki ,
You want to count distinct users who used each pair of stream names, using Stream Name (rows) and Stream Name1 (columns).
Solution: DAX Measure for Distinct User Count:
UserCount = CALCULATE( DISTINCTCOUNT('Table1'[User ID]), CROSSFILTER('Table1'[Stream Code], 'Table2'[Stream Code], BOTH) )Steps to Implement:
-
Ensure relationships: Table1[Stream Code] → Table2[Stream Code] (Many-to-One).
-
Create Matrix Visual:
- Rows: Table2[Stream Name]
- Columns: Table2[Stream Name1]
- Values: UserCount (above measure)
Thank you, I tried this measure but the result was as attached pic
-
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.Thank you
Thank you, within below link you will find the data:
https://docs.google.com/spreadsheets/d/1sqeFj_VjTEtFli9hranSLtn9c0CQ2HeE-3QoURlu_UU/edit?usp=sharing
Hi TamerOmki ,
Could you please provide sample data and expected output? That will help us provide you with the right solution.
Best regards,
Mengmeng Li
Thank you, within below link you will find the data:
https://docs.google.com/spreadsheets/d/1sqeFj_VjTEtFli9hranSLtn9c0CQ2HeE-3QoURlu_UU/edit?usp=sharing
