cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sau001
Helper IV
Helper IV

Is there any way I can group the records in a Table Visual (not Matrix)

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

sau001_1-1601761442899.png

 

 

After expanding a certain grouped row

sau001_0-1601761414283.png

 

 

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

 
 

 

 

 

6 REPLIES 6
stevedep
Super User I
Super User I

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. 

hcart.png

Please mark as solution if so. Thumbs up for the effort are appreciated.

Kind regards, 
Steve. 

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

sau001_1-1601827374512.png

 

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

sau001_0-1601827342224.png

 

Steps 

  1. Created a new static table "CarColumns"
  2. sau001_2-1601827552917.png

     

  3. Add the following new measures
    • ColValue => ColValue = SWITCH([currentcol],"FullName", MAX(EmployeeVehicles[FullName]), "CarMake",MAX(EmployeeVehicles[CarMake]), "Address", MAX(EmployeeVehicles[Address]),"Total rows",COUNT(EmployeeVehicles[EmpID]))
    • currentcol =>   currentcol = max(CarColumns[Column])
  4. Drop a Matrix Visual.
  5. Turned off => Row headers --> "Stepped layout"
  6. Turned on Row headers--> "+/- icons"
  7. Configure the Matrix as follows
    1. Rows => CarMake , EmpId
    2. Columns=> Column
    3. Values => ColValue
  8.  
     
  9. sau001_0-1601828096763.png

     

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 

 

EmpIdFullNameCarMakeCarColourAddressGender
101John DoeHondaRed1 Blah DriveMale
102Jane DoeHondaRed2 Blah DriveFemale
103John SmithFordRed3 Blah DriveMale
104Jane SmithFordBlue4 Blah DriveFemale
105George WashingtonHondaBlue5 Blah DriveMale
106Marie CurieFordBlue6 Blah DriveFemale
107Jane SmithMercedesBlue7 Blah DriveFemale

 

Steps

  1. Drag a Matrix visual to the page
  2. Turn off Stepped layout
  3. Turn on +/- icons
  4. Turn off Row totals
  5. Arrange the Columns, Rows and Values as shown 

sau001_1-1602017710751.png

 

Final outcome

sau001_2-1602017737531.png

 

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

 

 

 

 

lbendlin
Super User III
Super User III

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.

@lbendlin 

 

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

 

 

 

 

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

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.

Top Solution Authors
Top Kudoed Authors