Using Or function for Slicer selection

[Anonymous]

Hi everyone, 

Im trying to implement OR function in Slicer. Im aware when theres 2 slicers like open and close date, Powerbi filters out using AND function for both slicers. I would like to know if its possible to implement OR function. For example,

If any ID is opened or closed on the selected slicer date range, it should be displayed.

ID	Open	Close
11	01.01.23	01.02.23
234	01.01.23	02.02.23
43242	01.02.23	01.01.23
543543	02.02.23	01.01.23

When I select Open date 01.01.23, I get 

ID	Open	Close
11	01.01.23	01.02.23
234	01.01.23	02.02.23

When I select Open and Close date 01.01.23, I dont get any return value.

My expected outcome would be as below if i select Open and Close date 01.01.23

ID	Open	Close
11	01.01.23	01.02.23
234	01.01.23	02.02.23
43242	01.02.23	01.01.23
543543	02.02.23	01.01.23

[Anonymous]

Thank you all for helping me out, I managed to filter out the dates 🙂

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 

According to your description, you want to "Using Or function for Slicer selection" and get the count in the card visual.

Here are the steps you can refer to :
(1)This is my test data:

(2)We need to click "New Table" to create two tables as two slicers and we do not need to create any relationship between tables.

Open SLicer = VALUES('Table'[Open])

Close Slicer = VALUES('Table'[Close])

(3)Then we need to create two measures like this:

Flag = var _open_slicer = VALUES('Open SLicer'[Open])

var _close_slicer = VALUES('Close Slicer'[Close])

var _cur_open =  MAX('Table'[Open])

var _cur_close = MAX('Table'[Close])

return

IF(OR(_cur_open in _open_slicer , _cur_close in _close_slicer) ,1,-1)

Count = var _t =SUMMARIZE( ALLSELECTED('Table') ,'Table'[ID],'Table'[Open],'Table'[Close], "flag" , [Flag])

return

COUNTROWS(FILTER( _t ,[flag] =1))

The [Flag] measure is used to put on the "Filter on this visual" to control which the data need to be displayed.

The [Count] measure is used to put on the card visual to show the count you need.

(4)Then we put the fields on the visual and we can meet your need:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi thank you for your suggestion. I've followed your steps but it doesnt populate in my table. The values are correct when I display the count of open close dates in Card Visualisation. In my table it doesnt filter at all. Can you help me on this please?

[johnyip]

you should use the open /close date of your original data table in the table vis, instead of those in T1/T2.

Did you use the correct fields?

[Anonymous]

Yup I did. These are my steps
1. Changed MAIN table OpenDate & CloseDate as text and replaced null values with 1/1/1900

2. Created 2 DAX Tables T1 & T2 by using expression below

T1 = VALUES('MAIN'[OpenDate])

T2 = VALUES('MAIN'[OpenDate])

3. Formatted the dates as shortdates in T1 and T2

4. Created Measure in MAIN table

Measure =
IF(OR(MAX('MAIN'[OpenDate]) = FORMAT(SELECTEDVALUE('T1'[OpenDate]),"d/m/yyyy"),MAX('MAIN'[CloseDate]) = FORMAT(VALUE(SELECTEDVALUE('T2'[CloseDate])),"d/m/yyyy")),1,0)

5. Created Slicer using T1 and T2 dates

6. Created Table visualisation with ID, OpenDate, CloseDate from MAIN 

Please let me know if I've missed or did something wrong. Thank You.

[johnyip]

From your reply, I see you defined T2 wrongly, if that is not a typo.

And secondly, you missed the step "set filters on this visuals to include Measure = 1 "

This one single step is extremely crucial as it actually mimics the "OR" behaviours from slicers. WIthout that, the slicers you created won't do any filtering at all, since the underlying tables are standalone, having no relationships between the main table.

And you won't create any active relationships between main and the two tables, because this will render the data in main table being filtered away, making the data not display-able.

[johnyip]

@Anonymous 

To achieve this,

first, you need to create two extra tables, one with all the unique values of open date (T1) another one with unique values of closed date(T2).

I would use DAX to create the tables but you can use any other means you see fit.

DO NOT ASSIGN ANY RELATIONSHIPS BETWEEN THESE TABLES AND YOUR DATA.

For better display, I changed the format of open and closed date of 'Table' as text, and that of 'T1' and 'T2' as Date (short date). The following DAX of the measure is accustomed to this change. If you decide not to make this change, you need to modify the DAX to meet the data structure.

Next create a measure:

Measure =
IF(OR(MAX('Table'[Open Date]) = FORMAT(SELECTEDVALUE('T1'[Open Date]),"d/m/yyyy"),MAX('Table'[Closed Date]) = FORMAT(VALUE(SELECTEDVALUE('T2'[Closed Date])),"d/m/yyyy")),1,0)

After that, use 'T1'[Open Date] and 'T2'[Closed Date] in your slicers.

Finally, in your table, set filters on this visuals to include Measure = 1 (or whatever).

Please try to see if that works.

It works, in my own trial.