Dynamic measure based on filter context in a matrix (multiple dimensions)

[Anonymous]

Hi guys,

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:

Budget_Dynamic =
SWITCH(
   TRUE(),

   ISFILTERED(FactTable[LineItemID]),SUM(Ref_LineItem[Budget]),

   ISFILTERED(FactTable[InsertionOrderID]),SUM(Ref_InsertionOrder[Budget]),
   ISFILTERED(FactTable[AdvertiserID]),SUM(Ref_Advertiser[Budget]),
   "Else"
   )

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.

Can anybody help me?

[v-yuta-msft]

Hi GOEIEKOPER,

Modify you DAX formula like this and try again:

Budget_Dynamic =
SWITCH (
    TRUE (),
    HASONEFILTER ( FactTable[LineItemID] ), SUM ( Ref_LineItem[Budget] ),
    HASONEFILTER ( FactTable[InsertionOrderID] ), SUM ( Ref_InsertionOrder[Budget] ),
    HASONEFILTER ( FactTable[AdvertiserID] ), SUM ( Ref_Advertiser[Budget] ),
    "Else"
)

Regards,

Jimmy Tao

[Anonymous]

Hi @v-yuta-msft,

Unfortunately, this still doesn't work when I use the 'Drill mode'-toggle button (top right hand side of the matrix).

I think what happens is that when you drill-down on a certain Insertion Order, the dynamic value will result in the Insertion Order budget on Advertiser level as well, because this condition was met:

HASONEFILTER ( FactTable[InsertionOrderID] ), SUM ( Ref_InsertionOrder[Budget] ),

Not sure if this makes sense. I was thinking about a way to use a nested SWITCH function but can't really wrap my head around how I should go about this.

Kind regards,

Rocco