Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 ...
Tutorial Requests
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    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's avatar
Anonymous
Not applicable
3 years ago

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's avatar
johnyip
Solution Sage
3 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      johnyip
      Solution Sage
      3 years ago

      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.