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
cplesner
Helper III
Helper III

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

 

baseline.PNG

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 🙂

1 REPLY 1
v-chuncz-msft
Community Support
Community Support

@cplesner,

 

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.

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.