Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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?

 

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.