Forum Discussion
Make a slicer with AND function
Hello all,
For my report I need the user to be able to filter everything that is ONLY requested. So an AND function rather then an OR function. I will provide sample data below:
CustomerTbl
| ID | Customer |
| 1 | a |
| 2 | b |
| 3 | c |
ActionTbl
| ID | Area |
| 1 | BEL-162 |
| 1 | NLD-31 |
| 2 | NLD-31 |
| 2 | BEL-222 |
| 3 | NLD-31 |
| 3 | BEL-22 |
| 3 | BEL-222 |
AmountTbl
| ID | amount |
| 1 | € 1.500,00 |
| 2 | € 1.600,00 |
| 3 | € 1.800,00 |
The ID column represent transports. The area's are the places where the transport must load where there can be multiple. If there are multiple area's, all the areas must take place. The amount table represents the revenue for each transport. I need a slicer where the user filters the area's and the outcome (the ID's and therefore the amounts) only contains the selected area's.
So with current data, when the user filters for NLD-31, the user only wants to get a result with an ID where it ONLY contains NLD-31, so with this data wil return nothing. When the user selects NLD-31 and BEL-222, the user will only retreive ID = 2. When the user selects NLD-31 and BEL-222 and BEL-22, the user will only retreive ID = 3.
I hope my explanation is clear and please ask for more information if needed.
Thank you in advance!
Hi Anonymous ,
Here is the file you asked.
7 Replies
Ensure that your tables (CustomerTbl, ActionTbl, AmountTbl) are properly related through the ID column
DAX Measure:
SelectedOnlyMeasure =
VAR SelectedAreas = VALUES('ActionTbl'[Area]) // Get selected areas
VAR UniqueCount = DISTINCTCOUNT('ActionTbl'[Area]) // Count unique areas for the ID
VAR FilteredIDs =
CALCULATETABLE(
VALUES('ActionTbl'[ID]), // Get IDs based on the selected areas
FILTER(
'ActionTbl',
'ActionTbl'[Area] IN SelectedAreas
)
)
VAR MatchCount =
COUNTROWS(
FILTER(
'ActionTbl',
'ActionTbl'[ID] IN FilteredIDs // Only check the filtered IDs
)
)
RETURN
IF(
MatchCount = UniqueCount, // Check if count matches unique count
1, // Return 1 if they match
0 // Return 0 if they don't match
)Set the visual filter to show items where SelectedOnlyMeasure is equal to 1.
💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedInHi Anonymous ,
Add a table with the Area has a disconnected table then add the following measure to your model:
Validation = IF( CONCATENATEX( VALUES('Action'[Area]), 'Action'[Area], ",", 'Action'[Area] ) = CONCATENATEX( Area, Area[Area], ",", Area[Area], ASC ), 1 )Now add this as a filter on your visual an select is not blank the slicer should be based on the new table:
