DAX chaining filters in a calculate statement

[ppvinsights]

Hi community,

I want to introduce a segment analysis to a report. For that I introduced two segment dimensions - and I need some more. Like

• Number of employees (from,to)
• Monthly reccurring revenue (from, to)
• and so on

Because I do not want to rewrite all my measures I thought about using a calculation group. In the calculation group I just detect, if any of those "segment dimensions" are filtered and if so, I want to add a filter statement. And here is my problem: Is there an elegent way to add filter statements dynamically to a calculate statement?

I already tried something like this:

var filterMRR =
IF (
    ISFILTERED ( DimMRRSegment ),
    FILTER (
        DimAccount,
        [MRR] >= MIN ( DimMRRSegment[from] )
            && [MRR] <= MAX ( DimMRRSegment[to] )
    ),
    FILTER ( DimAccount, TRUE () )
)
var filterEmpl =
IF (
    ISFILTERED ( DimEmployeesSegment ),
    FILTER (
        DimAccount,
        DimAccount[NoOfEmployees] >= MIN ( DimEmployeesSegment[from] )
            && DimAccount[NoOfEmployees] <= MAX ( DimEmployeesSegment[to] )
    ),
    FILTER ( DimAccount, TRUE () )
)

RETURN

    CALCULATE(SELECTEDMEASURE(), filterMRR, filterEmpl)

But the if-statement does not allow tables as an return value.

But using something like that

var x =
FILTER (
    DimAccount,
    IF (
        ISFILTERED ( dimMRRSegement ),
        [MRR] >= MIN ( DimMRRSegment[from] )
            && [MRR] <= MAX ( DimMRRSegment[to] ),
        TRUE
    )
)

is not a good idea, because the "isfiltered" will be executed for every row in "DimAccount"

So: Is there any good practise to "chain" filters in a calculate statement?

Thanks a lot

Holger

[ppvinsights]

Sorry, I have to reopen the ticket. 

Did you try the dax statement in your answer...? Because right now I have a similar problem and tried to solve it your way and I receive an error:

Sorry, I had to wipe out the customer name from the measures. The return value is just "1" to be sure I have no other problem...

Thanks

Holger

[ppvinsights]

oh wow, thanks a lot - I always thought "switch" will translated internally to an if statement and so it want work neither. Thanks a lot!

[v-yueyunzh-msft · Accepted solution]

Hi , @ppvinsights 

According to your description, you want to check isfiltered for oen table , you can try to use SWITCH()  function to check if a filter is applied to each table and then apply the appropriate filter. Here is an example of how to do this:

VAR filterMRR =
    SWITCH (
        TRUE (),
        ISFILTERED ( DimMRRSegment ),
            FILTER (
                DimAccount,
                [MRR] >= MIN ( DimMRRSegment[from] )
                    && [MRR] <= MAX ( DimMRRSegment[to] )
            ),
        TRUE (),
            FILTER ( DimAccount, TRUE () )
    )
VAR filterEmpl =
    SWITCH (
        TRUE (),
        ISFILTERED ( DimEmployeesSegment ),
            FILTER (
                DimAccount,
                DimAccount[NoOfEmployees] >= MIN ( DimEmployeesSegment[from] )
                    && DimAccount[NoOfEmployees] <= MAX ( DimEmployeesSegment[to] )
            ),
        TRUE (),
            FILTER ( DimAccount, TRUE () )
    )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly