Use Same Filter for Multiple Columns

[Anonymous]

Hi,

I was wondering if there was a way to create a slicer for the same month and day, but used for different columns in a table.

In my table, there are two different dates. One for Inititated Month and Day and another for Repaired Month and Day

I am trying to create one single dashboard so that when using a slicer filter for a specific Month and Day, it filteres for both the Initiated and Repaired dates. I have not found a solution so I have created two separate slicer filters for Initiated and Repaired, but I feel like there is a better way to do this. 

Thank you!
Sarah

[Anonymous]

Hello everyone, 

I am creating a dashboard and my dataset has 4 slicers to filter the data.

Subjects	Rank1	Rank2	Rank3	Rank4
English	John	Clara	Rey	Rey
Maths	Peter	Roy	Noah
Science	Mark	Jezz	Jezz	Peyer

Currently , i have 4 filters for Rank1, Ran2, Rank3, Rank4.. My intention is to combine all 4 filters to one filter name student name and then when i filter any name in the slicer , whole dashboard chnages to it.

Many thanks in advance

[v-lid-msft]

Hi @Anonymous ,

First of all, we can create a spreated calculated table as te slicer

Slicer = DISTINCT(UNION(DISTINCT('Table'[Init Month and Day]),DISTINCT('Table'[Repaire Month and Day])))

Then we can create a measure and use it in visual filter to meet your requirement:

Filter In Date =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        [Init Month and Day] IN FILTERS ( Slicer[Month and Day] )
            || [Repaire Month and Day] IN FILTERS ( 'Slicer'[Month and Day] )
    )
)

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that we have shared.

Best regards,

[Anonymous]

Good Morning Dong ,

I have created the Slicer table as you showed:

Slicer = DISTINCT(UNION(DISTINCT(Query1[Init Month and Day]),DISTINCT(Query1[Repaired Month and Day]))), named Month and Day, and also the measure named Filter in Data: 

Filter in Data = CALCULATE(COUNTROWS(Query1),FILTER(Query1, Query1[Init Month and Day] IN FILTERS (Slicer[Month and Day]) || Query1[Repaired Month and Day] IN FILTERS (Slicer[Month and Day])))

I am struggling understanding how to use this filter, because I believe that there is no link between the above column and measure and the table 'Query 1' , so when using Month and Day as the field for the Slicer filter, it does not filter the tables as seen in the screenshot below...

Thank you so much!
Sarah 

[mwegener]

Hi @Anonymous ,

could you share a screenshot of your model view?

Is there only one Measure in your Matrix?

[Anonymous]

Also, @mwegener 

There is only one measure in my matrix, as seen below

Thank you!
Sarah

[mwegener · Accepted solution]

Hi @Anonymous ,

I have created a small example.

With USERELATIONSHIP you can switch the relationship for a measure.

https://docs.microsoft.com/en-us/dax/userelationship-function-dax

You may download my PBIX file from here.
Hope this helps.

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[gruskowska]

HI - thanks for the post.  Unfortunately I'm getting 404 file not found when I try to download your example file.  Would it be possible to repost or include the formlas the main post. Thanks!

[Anonymous]

This worked thank you so so much!!!
Sarah

[Anonymous]

Hi @mwegener 

This may be a dumn question, but how do you create the Value column in your query table?

Thank you!
Sarah

[mwegener]

Hi @Anonymous ,

The Value column is a simply entered column at my sample data.

I use the "Enter data" Option to create simple tables direct in Power BI Desktop.

[Anonymous]

Good Morning @mwegener !

I am new to Power BI... is this what you mean for model view?

The original table with Init Month and Day and Repaired Month and Day are in the Query1 table... I have been trying to play around and created the Datess, Dates, and Slicer tables and have tried linking them, but I cannot seem to create the correct link so that my filter filters for both Init and Repaired Month and Day...

Thank you so much!
Sarah

[mwegener]

Hi @Anonymous 

look at this

https://docs.microsoft.com/en-us/dax/userelationship-function-dax

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.