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?
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.
Firstly, many thanks for sharing your PBIX file. I followed your steps on a much simpler dataset.
Dataset - EmployeeVehicles
Group by "CarMake" column and display all columns for all the records under a specific group. Allow the user to progressively drill down.
The output looks to be as per expectations, disergarding how arduous and error prone the journey was.
Have I missed any of your steps?
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.
|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|
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 ?
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.
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps
Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.