Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Memorable Member
Memorable Member

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. 

Anonymous
Not applicable

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

 

Anonymous
Not applicable

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

 

 

 

 

 

Anonymous
Not applicable

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
Super User

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.

Anonymous
Not applicable

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.