OR Slicers in Direct Query

[kavurir1]

Hello,

I have a data table which has the same column which feeds into 2 slicers (for example same column has Male employee and Female Classification and we may have to compare data among Male vs Female). 2 Slicers are Male data and Female Data. (Male Teens, Male Young Adults, Male Middle Aged, Male Older, Female Teens, Female Young Adults, Female Middle Aged, Female Older)

When we are using Direct query, the moment we select an item in Male slicer, the data will filter only to Male slicer selection. And if we select both Male and Female slicers, no data will show up because both Slicers together will send the data back to data base with “AND”.

If we put the both Male and Female slicer data in one slicer, the data will come perfectly because PBI sends the query to database with “OR” or “IN”. This scenario is not possible because users need separate slicers.

Is there is way we can “OR” the two slicers?

[Yeriel]

Hello @kavurir1 !

Can you upload a copy of the file thus we can undestarnd the whole process and give a more accurate answer.

Cheers!

[EnterpriseDNA]

Hi @kavurir1, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

• Use the forum search to discover if your query has been asked before by another member.

• When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

• Use the proper category that best describes your topic

• Provide as much context to a question as possible.

• Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

[davidcenna]

As @Yeriel rightly said, you need to provide comprehensive information like your files and screenshot image to understand your data scenarios better, however if what am thinking about your questions is right, then SWITCH (TRUE() approach can work for the problem you’re trying to solve

[Harsh]

Hello kavurir1,

In the following way, you can convert your slicers into the "OR condition -

1. First break your table into the “Fact Table” and then the “Lookup Table” i.e. Info Table, Gender Table and Gender Category Table.

2. Secondly you can either create a column using “Calculated Column” or Add Column option using “Column from Examples”.

3. Create the following measure in your table and then drag the measure to the visual level filters and set it’s value to 1. Below is the measure provided -

    Check Measure = 
    VAR SelectedGender = 
    IF(
        ISERROR(
            SEARCH( SELECTEDVALUE( Gender[Gender] , 1 ) , 
                FIRSTNONBLANK( Info[Merged] , 1 ) ) ) , 
                0 , 
                1 )
   
    VAR SelectedGenderCategory =  
    IF(
        ISERROR(
            SEARCH( SELECTEDVALUE( 'Gender Category'[Category] , 1 ) , 
                FIRSTNONBLANK( Info[Merged] , 1 ) ) ) , 
                0 , 
                1 )
   
    RETURN
    IF( SelectedGender = 1 || SelectedGenderCategory = 1 , 1 , 0 )
   

Have also attached the PBIX file for the reference.

Hoping this solution meets your requirement.

Thanks and Warm Regards,
Harsh

exp.pbix (67.9 KB)

[BrianJ]

@kavurir1,

If you’re not averse to the use of custom visuals, you can do this directly in SmartFilter Pro by OK Viz (the SQLbi guys).

Here’s a summary of my experience with it:

Seeking Slicer and Search Box Design Advice

@Nick_M, I’ve been running the trial version of Smart Filter Pro through its paces for a while, and can report back that at least in my view it’s everything they say it is and more. It’s incredibly fast. Even when filtering on my largest dimension table (over 2,300 records) it responds instantaneously on the autofill and search. They say anything more than 30,000 records will slow it down, so just to see what happened, I filtered directly on my fact table of 230,000 records. It was a shad…

And a link to their webpage:

Introducing Smart Filter Pro - OKVIZ

Hope this is helpful.

• Brian

[EnterpriseDNA]

Hi @kavurir1, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!