Dynamic filtering measure

[FarhanJeelani]

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)

[v-pnaroju-msft]

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.

[v-pnaroju-msft]

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]

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.

[johnt75]

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 )

[AnkitaaMishra]

Hi @FarhanJeelani , Could you please share some sample data along with the expected output, so that the requirement is clearer.

[Jihwan_Kim · Accepted solution]

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 )