Filter with union instead of intersection Issue

[DatamirHub]

I have two slicers in Power BI, and my goal is for them to behave as a union, not an intersection.

For example:

• Slicer A returns 10 people

• When I select a value in Slicer B, I want it to add to the result

• So the total should become 12 people, not less than 10

I understand that this behavior is not supported natively in Power BI, since slicers always intersect filters.

Because of that, I created a filter measure that simulates a union and then used this measure to filter visuals.

This approach:

• Works perfectly for card visuals

• Fails for table visuals

For tables, it becomes extremely slow and eventually fails. This makes sense because:

• The filter measure is evaluated row by row

• For each row, the measure re-evaluates all filter logic again

I tried two different approaches (see below). Both are logically correct and give the expected results, but both are very slow when applied to a table.

My question is:

Is there a better or more efficient approach to achieve a union-style slicer behavior in Power BI without the performance issues caused by row-level filter measures on tables?

Codes

Solution 1:

_SHOW_ROW_FILTER_UNION =
VAR Filter1Applied =
    ISFILTERED ( filter_1_table[source_type] )
        || ISFILTERED ( filter_1_table[level_1] )
        || ISFILTERED ( filter_1_table[level_2] )
        || ISFILTERED ( filter_1_table[level_3] )

VAR Filter2Applied =
    ISFILTERED ( filter_2_table[category] )
        || ISFILTERED ( filter_2_table[sub_category] )
        || ISFILTERED ( filter_2_table[sub_sub_category] )

VAR CurrentPerson =
    SELECTEDVALUE ( fact_table[person_id] )

RETURN
IF (
    NOT Filter1Applied && NOT Filter2Applied,
    1,

    IF (
        Filter1Applied && NOT Filter2Applied
            && CurrentPerson
                IN VALUES ( filter_1_table[person_id] ),
        1,

        IF (
            Filter2Applied && NOT Filter1Applied
                && CurrentPerson
                    IN VALUES ( filter_2_table[person_id] ),
            1,

            IF (
                Filter1Applied && Filter2Applied
                    && (
                        CurrentPerson
                            IN VALUES ( filter_1_table[person_id] )
                        || CurrentPerson
                            IN VALUES ( filter_2_table[person_id] )
                    ),
                1,
                0
            )
        )
    )
)

Solution 2:

_SHOW_ROW_FILTER_UNION =
VAR Filter1Applied =
    ISCROSSFILTERED ( filter_1_table )

VAR Filter2Applied =
    ISCROSSFILTERED ( filter_2_table )

VAR Filter1Persons =
    FILTER (
        DISTINCT ( filter_1_table[person_id] ),
        Filter1Applied
    )

VAR Filter2Persons =
    FILTER (
        DISTINCT ( filter_2_table[person_id] ),
        Filter2Applied
    )

VAR UnionPersons =
    DISTINCT (
        UNION (
            Filter1Persons,
            Filter2Persons
        )
    )

VAR CurrentPerson =
    SELECTEDVALUE ( fact_table[person_id] )

RETURN
IF (
    NOT Filter1Applied && NOT Filter2Applied,
    1,
    IF (
        CONTAINS ( UnionPersons, [person_id], CurrentPerson ),
        1,
        0
    )
)

[ThxAlot]

Generic solution to your question,

Using OR conditions between slicers in DAX - SQLBI

[DatamirHub]

Thank you for the great articel unfortunately the first two approaches works for measure and not for filtering table except the third choice which i already tried above but its very performance extensive

[v-menakakota · Accepted solution]

Hi @DatamirHub ,

Thanks for reaching out to the Microsoft fabric community forum. 

At the moment, there is no official Microsoft documentation that describes a supported way to achieve UNION / OR behavior between slicers for table visuals.

The commonly shared approaches (including SQLBI examples) all use measure-based logic. While this works for cards and aggregated visuals, it causes performance issues for tables because the measure is evaluated row by row.

So the behavior you are seeing is expected and not related to an issue in your DAX. To avoid performance problems, the UNION logic needs to be handled at the model or data preparation level, or by redesigning the slicer approach.

If I misunderstand your needs or you still have problems on it, please feel free to let us know. 

Best Regards, 
Community Support Team

[FBergamaschi]

Hi @DatamirHub,

here

https://www.sqlbi.com/articles/using-or-conditions-between-slicers-in-dax/

you find a video version, which I think is very comfortable to watch and implement step by step

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[DatamirHub]

Thank you for the great articel unfortunately the first two approaches works for measure and not for filtering table except the third choice which i already tried above but its very performance extensive

[cengizhanarslan]

Please refer to the following topic which is solved before:

https://community.fabric.microsoft.com/t5/Desktop/How-to-access-intersection-of-two-cascading-slicer...

[DatamirHub]

Thanks for replying I read the solution you provided but its very similar to what i have above which can be very consuming to the performance in tables 

can you please let me know whats the difference between your provided approach and my first solution above ?