cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
GOEIEKOPER Regular Visitor
Regular Visitor

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
Community Support Team
Community Support Team

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

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

GOEIEKOPER Regular Visitor
Regular Visitor

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

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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors