Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
Hi @Anonymous, you'll need couple of things to achieve the desired result:
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 🙂
Hi @Anonymous, you'll need couple of things to achieve the desired result:
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 🙂
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
User | Count |
---|---|
99 | |
86 | |
78 | |
75 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |