Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I am exploring if I can do simple groupings of records displayed in a Table visual. I can best describe my grouping requirements using the pictures below.
Flat view before expanding
After expanding a certain grouped row
Please note that I have seen posts with a similar question where the suggestion was to use a Matrix. I am aware that Matrix will group by the column which is thrown into the Rows section, but how do I display other columns without aggregating ?
Will a Matrix visual really work? I do not think so, but happy to be proven wrong.
Any suggestions? Or any 3 rd party visuals that might improve the user experience?
Thanks,
Sau
Hi there,
You could take a look at this post. Link to file here.
This might be the layout you are looking for? pls see the file to see how to approach this. Just tag me in case of further questions.
Please mark as solution if so. Thumbs up for the effort are appreciated.
Kind regards,
Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Thank you @stevedep ,
Your report is fantastic. This is what a real world business report would look like.
I am trying to study your report and reproduce the steps for my scenario. Will get back to you shortly.
Thanks,
Sau
Hi @stevedep
Firstly, many thanks for sharing your PBIX file. I followed your steps on a much simpler dataset.
Dataset - EmployeeVehicles
Objective
Group by "CarMake" column and display all columns for all the records under a specific group. Allow the user to progressively drill down.
Final outcome
Steps
The output looks to be as per expectations, disergarding how arduous and error prone the journey was.
Have I missed any of your steps?
Thank you,
Sau
Hi @stevedep ,
I think I might have over complicated my report. I repeated the exercise but without the need for any custom DAX. Here are the steps. Let me know what you think.
Dataset
EmpId | FullName | CarMake | CarColour | Address | Gender |
101 | John Doe | Honda | Red | 1 Blah Drive | Male |
102 | Jane Doe | Honda | Red | 2 Blah Drive | Female |
103 | John Smith | Ford | Red | 3 Blah Drive | Male |
104 | Jane Smith | Ford | Blue | 4 Blah Drive | Female |
105 | George Washington | Honda | Blue | 5 Blah Drive | Male |
106 | Marie Curie | Ford | Blue | 6 Blah Drive | Female |
107 | Jane Smith | Mercedes | Blue | 7 Blah Drive | Female |
Steps
Final outcome
This has given me the outcome very close to what I had expected.
Question about your Accounting report
What was your need for creating the custom DAX measures ColValue and currentcol ?
Thanks,
Sau
So people told you to use a matrix, and you didn't want to take their advice?
A table visual is called a table visual because it produces a table. There are no expand/collapse options in a table.
The scenario that I have posted is fairly trivial in SSRS. I was hoping get that work in Power BI - either via Table Visual or Matrix Visual.
If the Matrix visual works - excellent. However, I was unable to do so.
It is possible that I am missing a step OR it is possible that this is a limitation of the Power BI.
I am expecting somebody on this forum with real world practical experience to respond to my solution.
Thank you,
Sau
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |