Duplicate filters on same page

[rvcedd]

Hello there,

I have the following requirement and I would appreciate your help

• I have a data model with 3 tables: Fact Table, Category Info and Location Info
• The user must be presented with 2 filters : "Category 1" and "Category 2"; this allows the user to compare 2 different categories
• The Average of each category must be displayed independently at the top 
• The 2 selected categories must also be compared side by side in a Date graph 
• The data must be available to slice via a 3rd slicer in the Location Table

What are the steps/tweaks that I need to make in order to get this working? The solution presented in this thread is not suitable as I do not have the ability to join all 3 tables. Is there a way to achieve this in a different way?

The ideal outcome would be as follows:

• User selects "Full Name A" as the First Category and "Full Name B" as the Second Category
• User selects "Dublin" as the location
• The left mesaure Average Displays 2; the right measure average displays 3
• The graph displays both Category A and Category B across time (this is the part that I can't get working, the 2 filters cancel eachother out rendering the graph empty)

See screenshot below. I am also attaching the sample Power BI file here. 

Any help would be much appreciated.

[v-easonf-msft · Accepted solution]

Hi , @rvcedd 

According to your description, you can try to follow these steps :

1)Create two calculated table :

slicer = DISTINCT(Table2[CategoryFullName])

slicer2 = DISTINCT(Table2[CategoryFullName])

 2)Create two measures to display the average of Category Value:

MeasureAvg1 =
VAR SLICER1 =
    VALUES ( slicer[CategoryFullName] )
RETURN
CALCULATE ( [MeasureAvg], FILTER ( Table1, Table1[Column] IN SLICER1 ) )

MeasureAvg2 =
VAR SLICER2 =
    VALUES ( 'slicer 2'[CategoryFullName] )
RETURN
CALCULATE ( [MeasureAvg], FILTER ( Table1, Table1[Column] IN SLICER2 ) )

3)Create  a new  measure to replace the value in the visual:

Measure=
VAR un =
    UNION (
        VALUES ( slicer[CategoryFullName] ),
        VALUES ( 'slicer 2'[CategoryFullName] )
    )
RETURN
    IF (
        ISFILTERED ( slicer[CategoryFullName] )
            && ISFILTERED ( 'slicer 2'[CategoryFullName] ),
        CALCULATE ( [MeasureAvg], FILTER ( Table1, Table1[Column] IN un ) ),
        BLANK ()
)

Here is a sample:

Url:  https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/EZ7xuKLmYolJuf3by11Ud...

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rvcedd]

Many thanks for your reply this is really useful.