Forum Discussion

rvcedd's avatar
rvcedd
Icon for Helper I rankHelper I
6 years ago
Solved

Duplicate filters on same page

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.

 

 

 

 

 

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    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/EZ7xuKLmYolJuf3by11UdjoBJMYe5F9bps1_qfqiFCjMCw?e=x0fhMU

     

    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.

2 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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/EZ7xuKLmYolJuf3by11UdjoBJMYe5F9bps1_qfqiFCjMCw?e=x0fhMU

     

    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's avatar
      rvcedd
      Icon for Helper I rankHelper I
      6 years ago

      Many thanks for your reply this is really useful.