Forum Discussion
Dynamic Table Matrix With Changeable Hierarchy
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
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.
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