Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Using Or function for Slicer selection

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.

 

IDOpenClose
1101.01.2301.02.23
23401.01.2302.02.23
4324201.02.2301.01.23
54354302.02.2301.01.23

 

When I select Open date 01.01.23, I get 

IDOpenClose
1101.01.2301.02.23
23401.01.2302.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

IDOpenClose
1101.01.2301.02.23
23401.01.2302.02.23
4324201.02.2301.01.23
54354302.02.2301.01.23
1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
Community Support

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:

vyueyunzhmsft_0-1675393036299.png

(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:
vyueyunzhmsft_1-1675393176988.png

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

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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

v-yueyunzh-msft
Community Support
Community Support

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:

vyueyunzhmsft_0-1675393036299.png

(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:
vyueyunzhmsft_1-1675393176988.png

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
Not applicable

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?

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?



Please mark my post as the solution if this answers your question!
Appreciate your Kudos !!
Anonymous
Not applicable

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.

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 "

 

johnyip_0-1675393632898.png

 

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.



Please mark my post as the solution if this answers your question!
Appreciate your Kudos !!
johnyip
Super User
Super User

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

 

johnyip_0-1675327274525.png

 

It works, in my own trial.



Please mark my post as the solution if this answers your question!
Appreciate your Kudos !!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.