cancel
Showing results for
Did you mean:
Regular Visitor

Using parameters to construct calculation filters

Hi

I'm trying to construct a Measure Calculation that is based on Parameters - if possible...

I have a standard model with Product, Customer and Date. And for comparrison purposes i also have a range of baseline dimensions that are related to the same column in the fact table.

Baseline Product

Baseline Date

Baseline Customer

All three tables for baseline have relationships to the fact table, but are set as inactive (while Product, Date and Customer tables are active)

Now, if the user is using one or more of the baseline table to filter ex. the "Baseline Sale" they should overrule the active counterpart, but if they are not filtered, the already active relationship should be used.

As there are currently 3 "baseline tables" there are 9 different combinations that I could do IF statements to handle although it's not going to be pretty, so i was wondering if Parameters could be used to "construct" the calculation - something like:

Baseline Sale =
VAR Parameter1 =
IF (
ISCROSSFILTERED ( 'Baseline Customer' ),
"USERELATIONSHIP('Baseline Customer'[Customer Code],Sales[Customer Code]),ALL(Customer)",
""
)
VAR Parameter2 =
IF (
ISCROSSFILTERED ( 'Baseline Date' ),
"USERELATIONSHIP('Baseline Date'[Date],Sales[Date]),ALL(Date)",
""
)
VAR Parameter3 =
IF (
ISCROSSFILTERED ( 'Baseline Products' ),
"USERELATIONSHIP('Baseline Products'[Product Code],Sales[Product Code]),ALL(Products)",
""
)
RETURN
CALCULATE ( SUM ( Sales[Sales] ), Parameter1, Parameter2, Parameter3 )

So if ex. Baseline Customer is crossfiltered, use Baseline Customer relationship instead otherwise do nothing etc. That way the final calculation might end up just using baseline relationship for ex. just Date and Product

Any ideas are welcome 🙂

Community Support Team

Re: Using parameters to construct calculation filters

As far as I know, this is not supported. You may submit an idea via https://ideas.powerbi.com/forums/265200-power-bi.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

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.

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!