Forum Discussion

FarhanJeelani's avatar
FarhanJeelani
Super User
11 months ago
Solved

Dynamic filtering measure

Hi Community members,

I want to calculate sales mix based on different totals, depending on slicer selections.

On my report, I have three slicers:

Betrieb

Cost Center

Cost Type

I created three separate denominator measures for each slicer (Betrieb denominator, Cost Center denominator, and Cost Type denominator).

The logic I need is:

If only Betrieb is selected → divide by Betrieb denominator.

If both Betrieb and Cost Center are selected → divide by Cost Center denominator.

If Betrieb, Cost Center, and Cost Type are selected → divide by Cost Type denominator.

Currently, the DAX I’m using always divides by the Betrieb denominator and only switches to Cost Center or Cost Type denominator if those slicers are selected individually, not in combination.

Dax Im using:
Salesmix_Dynamic =
VAR a = [SalesQty] // base measure for quantity
VAR HasBetrieb = ISFILTERED('1346-vW_Report_1346'[Betrieb]) || ISFILTERED('1346-vW_Report_1346'[BetriebName])
VAR HasCostCenter = ISFILTERED('1346-vW_Report_1346'[Cost_Center_id]) || ISFILTERED('1346-vW_Report_1346'[Cost_Center_name])
VAR HasCostType = ISFILTERED('1346-vW_Report_1346'[Costtype_id]) || ISFILTERED('1346-vW_Report_1346'[Cost_type_name])

VAR B = IF(HasBetrieb,[Denominator for betrieb],IF(HasCostCenter || HasBetrieb , [Denominator for Costcenter],IF(HasCostType || HasBetrieb || HasCostCenter, [Denominator for Costtype],HasBetrieb)))
 
RETURN
    DIVIDE(a, b)
Need Help
Tips and Tricks
  • Jihwan_Kim's avatar
    Jihwan_Kim
    11 months ago

    Hi,

    Please try something like below if it works.

     

    Salesmix_Dynamic =
VAR a = [SalesQty] // base measure for quantity
VAR HasBetrieb =
    ISFILTERED ( '1346-vW_Report_1346'[Betrieb] )
        || ISFILTERED ( '1346-vW_Report_1346'[BetriebName] )
VAR HasCostCenter =
    ISFILTERED ( '1346-vW_Report_1346'[Cost_Center_id] )
        || ISFILTERED ( '1346-vW_Report_1346'[Cost_Center_name] )
VAR HasCostType =
    ISFILTERED ( '1346-vW_Report_1346'[Costtype_id] )
        || ISFILTERED ( '1346-vW_Report_1346'[Cost_type_name] )
VAR B =
    SWITCH (
        TRUE (),
        HasBetrieb, [Denominator for betrieb],
        HasCostCenter && HasBetrieb, [Denominator for Costcenter],
        HasCostType && HasBetrieb
            && HasCostCenter, [Denominator for Costtype],
        [Denominator for betrieb]
    )
RETURN
    DIVIDE ( a, b )

6 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    11 months ago

    Hi,

    Please try something like below if it works.

     

    Salesmix_Dynamic =
VAR a = [SalesQty] // base measure for quantity
VAR HasBetrieb =
    ISFILTERED ( '1346-vW_Report_1346'[Betrieb] )
        || ISFILTERED ( '1346-vW_Report_1346'[BetriebName] )
VAR HasCostCenter =
    ISFILTERED ( '1346-vW_Report_1346'[Cost_Center_id] )
        || ISFILTERED ( '1346-vW_Report_1346'[Cost_Center_name] )
VAR HasCostType =
    ISFILTERED ( '1346-vW_Report_1346'[Costtype_id] )
        || ISFILTERED ( '1346-vW_Report_1346'[Cost_type_name] )
VAR B =
    SWITCH (
        TRUE (),
        HasBetrieb, [Denominator for betrieb],
        HasCostCenter && HasBetrieb, [Denominator for Costcenter],
        HasCostType && HasBetrieb
            && HasCostCenter, [Denominator for Costtype],
        [Denominator for betrieb]
    )
RETURN
    DIVIDE ( a, b )
  • johnt75's avatar
    johnt75
    Super User
    11 months ago

    The issue is the order in which you are specifying the conditions. You need to specify the strictest first, otherwise the HasBetrieb clause will always trigger first.

    Try

    Salesmix_Dynamic =
VAR a = [SalesQty] // base measure for quantity
VAR HasBetrieb =
    ISFILTERED ( '1346-vW_Report_1346'[Betrieb] )
        || ISFILTERED ( '1346-vW_Report_1346'[BetriebName] )
VAR HasCostCenter =
    ISFILTERED ( '1346-vW_Report_1346'[Cost_Center_id] )
        || ISFILTERED ( '1346-vW_Report_1346'[Cost_Center_name] )
VAR HasCostType =
    ISFILTERED ( '1346-vW_Report_1346'[Costtype_id] )
        || ISFILTERED ( '1346-vW_Report_1346'[Cost_type_name] )
VAR b =
    SWITCH (
        TRUE (),
        HasCostType || HasBetrieb
            || HasCostCenter, [Denominator for Costtype],
        HasCostCenter || HasBetrieb, [Denominator for Costcenter],
        HasBetrieb
    )
RETURN
    DIVIDE ( a, b )
  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    11 months ago

    Thankyou, Jihwan_Kim, AnkitaaMishra and johnt75 for your responses.

    Hi FarhanJeelani,

    We appreciate your question on the Microsoft Fabric Community Forum.

    We kindly request you to provide sample data that clearly demonstrates your issue or query in a structured format (not as an image) to help us understand and resolve the matter. Please ensure that the data is relevant, free from any sensitive information, and directly related to the issue. Additionally, please share the expected outcome based on the given example.

    Thank you.

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    11 months ago

    Hi FarhanJeelani,

    Please share some sample data that shows your problem or question clearly in a simple and organized way (not as an image). This will help us understand and solve the issue better. Make sure the data is relevant, does not have any sensitive information, and is related to your problem. Also, please tell us what result you expect from this example.

    Thank you.

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    11 months ago

    Hi FarhanJeelani,

    Kindly provide some sample data which explains your problem or question clearly in a simple and neat format, not as an image. This will help us to understand and solve the issue more effectively. Please ensure the data is relevant, does not have any sensitive details, and is connected to your problem. Also, let us know what result you expect from this sample.

    Thank you.