Forum Discussion
Return Items that have Neither Item Selected
A DAX Puzzler: I have an inventory problem I'm trying to solve. Each location has inventory in a table TableQOHRemoveZeros. If I select the items in the slicer, I want it to return locations that have NEITHER item. E.g. if I select iPhone 12 and iPhone 13 in the Slicer, the only location that should return is Location 114 Plum Lane.
DISTINCT(TableQOHRemoveZeros[Device Name Short])
I thought that I could query this table after the items were selected against all of the locations using Except, but I don't think it's possible to query a table created by slicer selection.
LocationsWithoutSelectedDevices2 = VAR SelectedLocations= ALLSELECTED(DisDeviceNameShort[Device Name Short]) VAR AllLocations = All('Current Hierarchy'[Sales Code]) Var ExcludeSelectedLocation = EXCEPT(SelectedLocations,AllLocations) Return (ExcludeSelectedLocation)
Error:"A table with multiple values was returned when a single value expected" A real doozy. Any way to achieve this? I think it would have huge benefit to others as well and would greatly streamline a process like this.
| Sales Code | Location | Device Name Short |
| 111 | 123 Main St | iPhone 12 |
| 111 | 123 Main St | Iphone 16 |
| 111 | 123 Main St | Iphone 11 |
| 112 | 111 Elm St | Iphone 10 |
| 112 | 111 Elm St | Iphone 13 |
| 112 | 111 Elm St | iPhone 14 |
| 114 | 800 Plum ln | iPhone 15 |
| 114 | 800 Plum ln | iPhone 16 |
| 114 | 800 Plum ln | iPhone 17
|
Hi,
I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
Deviace Name short: = VAR _slicerselect = VALUES ( Slicer[Device Name Short] ) VAR _salescodelist = SUMMARIZE ( FILTER ( ALL ( TableQOHRemoveZeros ), TableQOHRemoveZeros[Device Name Short] IN _slicerselect ), TableQOHRemoveZeros[Sales Code] ) VAR _others = EXCEPT ( SUMMARIZE ( ALL ( TableQOHRemoveZeros ), TableQOHRemoveZeros[Sales Code] ), _salescodelist ) RETURN CALCULATE ( MAX ( TableQOHRemoveZeros[Device Name Short] ), FILTER ( TableQOHRemoveZeros, TableQOHRemoveZeros[Sales Code] IN _others ) )
6 Replies
Hi,
I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
Deviace Name short: = VAR _slicerselect = VALUES ( Slicer[Device Name Short] ) VAR _salescodelist = SUMMARIZE ( FILTER ( ALL ( TableQOHRemoveZeros ), TableQOHRemoveZeros[Device Name Short] IN _slicerselect ), TableQOHRemoveZeros[Sales Code] ) VAR _others = EXCEPT ( SUMMARIZE ( ALL ( TableQOHRemoveZeros ), TableQOHRemoveZeros[Sales Code] ), _salescodelist ) RETURN CALCULATE ( MAX ( TableQOHRemoveZeros[Device Name Short] ), FILTER ( TableQOHRemoveZeros, TableQOHRemoveZeros[Sales Code] IN _others ) )This is amazing! Would it be possible for you to tweak it to only show the location and not all the device names? A distinct location returned for locations that have neither device. So it would return like this: Sales Code Location
114 800 Plum LaneHi,
Thank you for your message, and one of ways is to put the measure to the Filter Pane -> Filters on this visual area, like the below image.
Please check the below picture and the attached pbix file.
Measure:
LocationsWithoutSelectedDevices =
VAR SelectedDevices = ALLSELECTED(TableQOHRemoveZeros[Device Name Short])
VAR LocationsWithSelectedDevices =
SUMMARIZE(
FILTER(
TableQOHRemoveZeros,
TableQOHRemoveZeros[Device Name Short] IN SelectedDevices
),
TableQOHRemoveZeros[Sales Code]
)
VAR AllLocations = VALUES(TableQOHRemoveZeros[Sales Code])
VAR LocationsWithoutDevices =
EXCEPT(
AllLocations,
LocationsWithSelectedDevices
)
RETURN
IF(
SELECTEDVALUE(TableQOHRemoveZeros[Sales Code]) IN LocationsWithoutDevices,
1,
0
)💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
