Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
Seasons greetings to everyone! I'm hoping someone could help me with something I feel is pretty complex, but it's probably a simple fix!
I have a power query table with the following data (snippet below and fuller example of dataset attached)
ID | Date | Total Trg Hours Total | Avoiding Action Score | Scan Score | Division of Attention Score |
54 | 01/12/2021 | 01:45 | -2 | 0 | 0 |
56 | 03/12/2021 | 02:00 | 1 | 0 | |
57 | 03/12/2021 | 02:30 | 0 | -1 |
I would like to show a COUNT of the data in a matrix table in the following format.
-2 | -1 | 0 | 1 | |
Avoiding Action | 1 | 0 | 1 | 0 |
Scan | 0 | 0 | 2 | 0 |
Division of Attention | 0 | 1 | 1 | 0 |
To give a better example of just what I'm looking for, see PDF link of the report the table will be going into. I've managed to recreate exactly what I'm looking for using a UNION table, but because there's no relationships, it's not filterable against the rest of the report (problem!!)
Really hope someone can help with this.
Thanks in advance and Happy New Year!
Solved! Go to Solution.
@jamesbb93 , Unpivot the columns from F to AB in your excel in power query, then you have both headers and values as columns, you can use them in matrix as row and column and count of any one as values
https://radacad.com/pivot-and-unpivot-with-power-bi
Hi,
Please check the below picture and the attached pbix file.
Result: =
CALCULATE (
COUNTROWS ( FILTER ( 'Table', 'Table'[Value] <> BLANK () ) ),
FILTER ( 'Table', 'Table'[Value] = MAX ( 'Table'[Value] ) )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@jamesbb93 , Unpivot the columns from F to AB in your excel in power query, then you have both headers and values as columns, you can use them in matrix as row and column and count of any one as values
https://radacad.com/pivot-and-unpivot-with-power-bi
@amitchandak @Jihwan_Kim I wasn't expecting such quick replies.
I went with the Unpivot route as it worked better with my data model.
Really appreciate the help!
User | Count |
---|---|
90 | |
74 | |
67 | |
63 | |
55 |
User | Count |
---|---|
102 | |
93 | |
73 | |
60 | |
59 |