Power BI Hack – Maintain Drill Level When Switching Field Parameters – Part 2

I made a previous post about the maintaining drill level in visuals when changing field parameters. That was a useful hack, but it only works for certain scenarios. What do you do if that still doesn’t work? You go old school! Field parameters didn’t exist in Power BI prior to May 2022 as a preview feature and it wasn’t fully supported until October 2024. So what did we do before that?

Well gather ’round children and let me show you how we handled it in the before times!

Prior to having field parameters, the method of dynamically displaying fields was by using disconnected tables and measures that utilise the SWITCH and TREATAS functions. Let me explain:

The first step is to create a calculated table with a summary of the fields we are going to switch between.

REF Product Segment = 
var _Product = SUMMARIZE(financials, financials[Product], "Type", "Product")
var Segment = SUMMARIZE(financials, financials[Segment], "Type", "Segment")

RETURN
UNION(_Product, Segment)

In the example above we’re summarising the Product and Segment Fields and creating a new summary table. This results in a table the looks like the following:

Ordinarily I would rename Product to something more suitable, like [Product or Segment], but i’ve kept it simple here for clarity. The Type column will be used to populate our slicer that lets the user choose what to display.

The next step is to create a measure that utilises this table:

Sum of COGS by Type = 
SWITCH(SELECTEDVALUE('REF Product Segment'[Type]),
    "Product", CALCULATE(SUM(financials[COGS]), TREATAS(VALUES('REF Product Segment'[Product]), financials[Product] )),
    "Segment", CALCULATE(SUM(financials[COGS]), TREATAS(VALUES('REF Product Segment'[Product]), financials[Segment] ))
)

Let’s break down what this is doing:

1. SWITCH(SELECTEDVALUE('REF Product Segment'[Type])
   This line sets the context of what the user has selected in the slicer. In this case is the Type Product or is it Segment.
   
2. "Product", CALCULATE(SUM(financials[COGS]), TREATAS(VALUES('REF Product Segment'[Product]), financials[Product] )),
   This line calculates the COGS for a given product. TREATAS acts as a role-playing function. It tells the DAX query processor to treat the values in 'REF Product Segment'[Product]) as though they were a value in financials[Product]. Creating a proxy for the join.
   
3. The final line is doing repeating step 2 for the Segment column.

Once we’ve created all of these elements, all that’s left is to place them in the visuals.

Even though this is the “Old School” way of doing it, it’s still required in some scenarios due to some limitations in the way the Power BI team have chosen to implement field parameters.