cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sk15227
Post Patron
Post Patron

Use Same Filter for Multiple Columns

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

Capture.PNG

 

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. 

 

Capture1.PNG

 

Thank you!
Sarah

1 ACCEPTED SOLUTION

Hi @sk15227 ,

 

I have created a small example.

 

TwoDatesOneFilter.png

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


View solution in original post

10 REPLIES 10
v-lid-msft
Community Support
Community Support

Hi @sk15227 ,

 

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] )
    )
)

 

14.jpg15.jpg16.jpg


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


Best regards,

 

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

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...

Capture.PNG

 

Thank you so much!
Sarah 

Hi @sk15227 ,

 

could you share a screenshot of your model view?

Is there only one Measure in your Matrix?

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


Also, @mwegener 

 

There is only one measure in my matrix, as seen belowCapture.PNG

 

 

Thank you!
Sarah

Hi @sk15227 ,

 

I have created a small example.

 

TwoDatesOneFilter.png

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


View solution in original post

This worked thank you so so much!!!
Sarah

Hi @mwegener 

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

 

Thank you!
Sarah

Hi @sk15227 ,

 

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


Good Morning @mwegener !

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

 

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
Super User
Super User

Hi @sk15227 

 

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...

 

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

Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.