Make a slicer with AND function

[bnjmnnl]

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! 

[MFelix]

Hi @bnjmnnl ,

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:

[bnjmnnl]

Because I get this error

[MFelix]

Hi @bnjmnnl,

Can you share the measure you have created?

It seems like a error on the code. Maybe a comma or lack of it somewhere. 

[bnjmnnl]

Thank you for the answer! Can you please show the model view of your suggestion?

[MFelix · Accepted solution]

Hi @bnjmnnl ,

Here is the file you asked.

[Kedar_Pande]

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 LinkedIn

[bnjmnnl]

Thank you! However this measure doe not make it happen so that the visual only displays the value where the selection is set to. If there are more area's and only one is selected it still shows that as the answer..