I'm having some issues with filter context in the matrix visual when using multiple levels as dimensions.
My current setup is as follows, I have:
- A fact table where data is grouped on 3 levels:
- Advertiser - Insertion Order
- Line Item
These levels are ID's, so formatted as integers. Their names and other information like budgets and billing periods can be found in reference tables. So we also have three of those:
- A reference table for Advertiser
- A reference table for Insertion Order
- A reference table for Line Item
Now, what I want is to have all those levels in a matrix and be able to drill-down. When drilling down, I want to show the budget dynamically. So in the lines where an Advertiser ID is stated, I want to show the budget from the Advertiser reference table. In lines where an Insertion Order ID is stated, I want to show the budget from the Insertion Order reference table.
At the moment I'm using a measure in which I use a SWITCH function combined with an ISFILTERED function. The measure looks like this:
This works like a charm as long as I use the 'Expand all down one level in the hierarchy'.
But, when I use the 'Drill-down'-toggle button and click on an Insertion Order, all budgets on higher levels change to that of the Insertion Order. This is because the condition ISFILTERED(FactTable[InsertionOrderID]) is met in that case, which basically shows wrong data.
I have the feeling that I'm approaching this wrong and there should be an easier way to solve this.