Dynamic Table Matrix With Changeable Hierarchy

[STEFAN_SA]

Basic Explenation:
Table consists of financial data based over the year of 2024. Month name [dimDate][Month] is added under columns. Sum Amount added under values [fctTransanctions][Amount]
Rows Grouping
L1: Spending Group - [dimSpendingGroup][Spending Group] - Relationship with [fctTransanctions][Spending Group] - M to 1,Single
L2: Category Type - [dimCategory][CategoryType] - Relationship with [fctTransanctions][Category] - M to 1,Single
L3: Category - [dimCategory][Category]- Relationship with [fctTransanctions][Category] - M to 1,Single

Objective:
To create a slicer that will assist in changing the grouping of ROWS in a Table Matrix Visual, and the order of the drill downs.
Changing the Levels, 1 2 or 3,

Example
Category > Category Type > Spending Group
or Category Type > Spending Group > Category
And so on. Giving the user the ability to change how they want to see the matrix grouped by.

Methods tried:
Dynamic table on fctTransactions: Creating a rowlevel table 1 to 3.
this does not create a drill down but helped in keeping the data in place and was only able to change level 1

Dynamic Table not linked:
Causes visual to break asking for relationsip linking. Cant add columns then visual breaks, Cant add hierarchy then it breaks.
only level 1 works with Values.

Measure:
Unable to add to the data to rows. Can only add to values.

Using PowerBI RS not Desktop version so using a What  If Field parameter works on Level 1 only on desktop but is not availible to use in RS.

Not sure this is possible. Though I will ask the community.

[STEFAN_SA]

Not the solution as there is still issues. But okay.

[bhanu_gautam]

@STEFAN_SA Create a table that lists the possible hierarchy levels. This table will not be connected to your data model.

HierarchyLevels =
DATATABLE(
"Level", STRING,
{
{"Spending Group"},
{"Category Type"},
{"Category"}
}
)

Add a slicer to your report using the HierarchyLevels table. This slicer will allow users to select the hierarchy levels they want to use.

Create DAX measures to dynamically select the hierarchy levels based on the slicer selection. 

SelectedLevel1 =
SELECTEDVALUE(HierarchyLevels[Level], "Spending Group")

SelectedLevel2 =
SWITCH(
SELECTEDVALUE(HierarchyLevels[Level]),
"Spending Group", "Category Type",
"Category Type", "Category",
"Category", "Spending Group"
)

SelectedLevel3 =
SWITCH(
SELECTEDVALUE(HierarchyLevels[Level]),
"Spending Group", "Category",
"Category Type", "Spending Group",
"Category", "Category Type"
)

Use the measures to create a dynamic table that will display the data based on the selected hierarchy levels:-

DynamicTable =
SUMMARIZE(
fctTransactions,
SWITCH(
[SelectedLevel1],
"Spending Group", dimSpendingGroup[Spending Group],
"Category Type", dimCategory[CategoryType],
"Category", dimCategory[Category]
),
SWITCH(
[SelectedLevel2],
"Spending Group", dimSpendingGroup[Spending Group],
"Category Type", dimCategory[CategoryType],
"Category", dimCategory[Category]
),
SWITCH(
[SelectedLevel3],
"Spending Group", dimSpendingGroup[Spending Group],
"Category Type", dimCategory[CategoryType],
"Category", dimCategory[Category]
),
"Amount", SUM(fctTransactions[Amount])
)

Use the DynamicTable in your matrix visual. Place the dynamic columns in the rows section and the Amount measure in the values section.

[STEFAN_SA · Accepted solution]

Thanks for the feedback, The setup look familiar, however i deleted everything I tried and started again with your approach.
All was working till I got to the Dynamic Table.
1st error:
Function SUMMARIZE expects a column name as argument number 2.
2nd error:
A single value for column 'CategoryType' in table 'dimCategory' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Replaced with the below

DynamicTable =
ADDCOLUMNS(
fctTransactions,
"Level1",
SWITCH(
[SelectedLevel1],
"Spending Group", fctTransactions[Spending Group],
"Category Type", LOOKUPVALUE(dimCategory[CategoryType], dimCategory[Category], fctTransactions[Category]),
"Category", fctTransactions[Category]
),
"Level2",
SWITCH(
[SelectedLevel2],
"Spending Group", fctTransactions[Spending Group],
"Category Type", LOOKUPVALUE(dimCategory[CategoryType], dimCategory[Category], fctTransactions[Category]),
"Category", fctTransactions[Category]
),
"Level3",
SWITCH(
[SelectedLevel3],
"Spending Group", fctTransactions[Spending Group],
"Category Type", LOOKUPVALUE(dimCategory[CategoryType], dimCategory[Category], fctTransactions[Category]),
"Category", fctTransactions[Category]
),
"Total Amount", fctTransactions[Amount] -- Renamed to "Total Amount"
)

In the visual I used Level1, Level2, Level3 in Rows and "Total Amount" in Values.

1. Had to remove the dimDate[MonthName] column as I recieved the error "Can't determain Relationship"
 - because of your way of doing the Dynamic table I was able to link the dimDate to this table to resolve the error.
2. No data for Level 2 or 3, also Level 1 seem partialy incorrect but I think it has to do with the relationships again.
3. Slicer is not chaning anything. I see level 1, then Level 2 and 3 is blank