Filter data based on two condition with included and not included

[Anonymous]

Hi,

I want to have two slicers based on the same column in PowerBI to filter my data.

The dataset is following:

The slicers are based on the column "Used_Function". With the first slicer I want to get all users which used the function. And the second slicer defines the Users which has not used the function. 

For example, if I want all users which has used the function "strategy" but not "Settings", then I want the names "Max", "Max", "Milian", "Milian".

How can I solve this task in PowerBI?

I want to have a dropdown were I can select products and a visual where I can see the users which havent bought this product in the selected time. 

[Sergii24 · Accepted solution]

Hi @Anonymous, you'll need couple of things to achieve the desired result:

1. Disconnected table with unique values of "Used_Function"
2. Measure that will combine filters applied on "Used_Fucntion" from both tables
3. Add a newly created measure as a visual level filter to a table with UserID

First, the disconnected table:

FilteringQuestion_DisconnectedFunctions = DISTINCT( FilteringQuestion[Used_Function] )

Now, the measure:

Filter_Measure = 
VAR _DisconnectedFunction = VALUES( FilteringQuestion_DisconnectedFunctions[Used_Function] )            //get the list of fucntions to exclude
VAR _DisonnectedUsers =                                                                                 //get a table of 1 column of users who have values for currently selected disconnected function
    SELECTCOLUMNS(
        FILTER(
            ALL( FilteringQuestion[UserID], FilteringQuestion[Used_Function] ),
            FilteringQuestion[Used_Function] in _DisconnectedFunction
        ),
        "@Users", FilteringQuestion[UserID]
    )

RETURN 
    IF(
        SELECTEDVALUE( FilteringQuestion[UserID] ) in _DisonnectedUsers,
        1,
        0
    )

The final step is apply a visual level filter on both tables (Filter_Measure = 0 to the one on the left and Filter_Measure = 1 to the one on the right)

Let me know if it works as you expect 🙂

[Anonymous]

Hi Sergii24,

thanks for helping me out.

I need to expand the functionallity, to include a date filter in this page. 

In your solution we have the code part:

ALL( FilteringQuestion[UserID], FilteringQuestion[Used_Function] )

And I found out, because of this ALL() it is not filtering correctly. 

For example, if I select all users since beginn of this year, and the user "ABC" only used "Function_1", but before he used "Function_2". I also need to select on the right slicer "Function_2", then he shows me user "ABC". 

Hope my example is clear. Could you help me out again? 

Best regards