Need help in Power BI Matrix Visualization hierarchy to ignore at second level.

[srbhdix]

Hello PBI Experts, 

We need some help in achieving below output table in power bi matrix visualization. We have also mentioned the input table below. Request you all experts, if you have any workaround then please feel free to reply on this mail or connect with me to help us on this requirement.  

Note:  We have tried matrix visualization in power bi and we have noticed that when we add the first column in rows it group by 1st category and then when we add second column by default it does group by on 2nd category however the requirement here is to not to group by on 2nd category and show all the rows associated to that categories. If we turned off the stepped layout on row headers, then the second category comes in columns but that's not meeting the requirement.

Output: 

Meeting Invitee & Subject	Meeting-Start Time	Meeting-EndTime	Meeting Category
Abc@test.com
testsub1	1/1/2020 7:30	1/1/2020 8:00	Cat1
testsub2	1/12/2022 13:00	1/12/2022 13:30	Cat2
testsub3	8/8/2022 2:00	8/8/2022 2:30	Cat3
testsub4	8/8/2022 3:00	8/8/2022 3:30	Cat1
testsub5	8/9/2022 3:00	8/9/2022 3:30	Cat2
DEF@test.com
testsub10	8/14/2022 2:00	8/14/2022 2:30	Cat1
testsub6	8/10/2022 3:00	8/10/2022 3:30	Cat3
testsub7	8/11/2022 2:00	8/11/2022 2:30	Cat1
testsub8	8/11/2022 3:00	8/11/2022 3:30	Cat2
testsub9	8/13/2022 2:00	8/13/2022 2:30	Cat3
GHI@Test.com
testsub11	8/15/2022 2:00	8/15/2022 2:30	Cat2
testsub12	8/16/2022 2:00	8/16/2022 2:30	Cat3
testsub13	8/17/2022 2:00	8/17/2022 2:30	Cat1
testsub14	8/17/2022 3:00	8/17/2022 3:30	Cat2
testsub15	8/18/2022 2:00	8/18/2022 2:30	Cat3
testsub16	8/19/2022 2:00	8/19/2022 2:30	Cat1
testsub17	8/19/2022 3:00	8/19/2022 3:30	Cat2
GHI2@Test.com
testsub16	8/19/2022 3:00	8/19/2022 3:30	Cat2
testsub16	8/19/2022 2:00	8/19/2022 2:30	Cat1

Input table:

[srbhdix · Accepted solution]

Please Find Below  following steps to achieve the requirement:                                                                                           1) i have created one POC as per the requirement. taken matrix table PFB measures which i have used for this POC.

2) i have created 4 measures i have added in to values of the matrix so that we can achieve the requirement . PFB measures for reference.

3) so we need to show and hide the data while user collapse the " +"  icon , wrote one measure " INSCOPE "  

4) subject and unique id not in same line ,it's coming in different column , we need to have the same column as per the requirement, so i change to the matrix visual properties and sync the visual.

5 ) one problem:   i faced that when we collapse the first level of hierarchy  we were the getting data , but we need to hide that when we collapse everything ,  so we have used measure to perform the calculation only when we expand the selection. the following is the measure used to perform the calculations.

6) In data model we have to select the - Row Label " SUBJECT " &  KEY COLUMN " INDEX "

1) Collapse & Hide Subject  = if(ISINSCOPE(Invitee[Subject -1]),SELECTEDVALUE(Invitee[Subject -1]), BLANK())  

2) Subject = LOOKUPVALUE('DMV Meetings'[Subject],'DMV Meetings'[uniqueID],Invitee[uniqueID])

3) ESTART-DATE = if(ISINSCOPE(Invitee[Subject -1]),SELECTEDVALUE(Invitee[EventStartDate]), BLANK())

4) EEND-DATE = if(ISINSCOPE(Invitee[Subject -1]),SELECTEDVALUE(Invitee[EventEndDate]), BLANK())

5) EP-CATEGORY = if(ISINSCOPE(Invitee[Subject -1]),SELECTEDVALUE(Invitee[PrimaryCategory]), BLANK()) 

[amitchandak]

@srbhdix , if you do want to group by, you need to use a table visual (not summarized) or add an index(primary key)  column in visual

Power BI- Power Query Table.AddIndexColumn- https://youtu.be/KEW4bbuqbV8

[srbhdix]

Hello Amit, 

Thanks for the suggestion and response. Appreciate the same. However this does not meet the requirement if we use the table visual then the second hierarchy will come in column and the layout will get change. In terms of adding index we need to concate that with second hierarchy which is "column1" in this case. Which also will not meet the requirement. Could you please suggest a better way to achieve this without changing the format output layout