Forum Discussion
Dynamic Table Matrix With Changeable Hierarchy
- 1 year ago
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
Not the solution as there is still issues. But okay.