topic Return Items that have Neither Item Selected in DAX Commands and Tips

Return Items that have Neither Item Selected

BrianNeedsHelp — Sun, 22 Dec 2024 21:12:35 GMT

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

Re: Return Items that have Neither Item Selected

Jihwan_Kim — Mon, 23 Dec 2024 03:55:25 GMT

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 ) )

Re: Return Items that have Neither Item Selected

BrianNeedsHelp — Mon, 23 Dec 2024 04:43:51 GMT

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 Lane

Re: Return Items that have Neither Item Selected

Jihwan_Kim — Mon, 23 Dec 2024 05:04:02 GMT

Hi,

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.

Re: Return Items that have Neither Item Selected

BrianNeedsHelp — Mon, 23 Dec 2024 05:07:43 GMT

You are amazing Jihwan_Kim ! Thank you so much-This is so awesome!

Re: Return Items that have Neither Item Selected

Kedar_Pande — Mon, 23 Dec 2024 05:46:29 GMT

BrianNeedsHelp

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

Re: Return Items that have Neither Item Selected

BrianNeedsHelp — Tue, 31 Dec 2024 18:48:42 GMT

Kedar_Pande Jihwan_Kim I combined several table columns together using Summarize to get into one table. I'm having issues getting everything to display properly. So I went back to try and use this code with the original tables, and I'm getting error "expression refers to multiple columns.Multiple columns cannot be converted to a scalar value." Not sure I formatted this correctly, but the only wat it would allow me to use other tables was to type RELATEDTABLE.

LocationsWithoutSelectedDevices1 = VAR _slicerselect = VALUES ( DisDeviceName[Device Name] ) VAR _salescodelist = SUMMARIZE (FILTER ( ALL ('Location'), Filter(RELATEDTABLE( 'Item Code'),'Item Code'[Device Name] IN _slicerselect) && Filter(RELATEDTABLE('Device Inventory On Hand'),'Device Inventory On Hand'[Device Inventory QOH])<>0 ), Location[Sales Code] ) VAR _others = EXCEPT ( SUMMARIZE ( ALL ( Location ), Location[Sales Code] ), _salescodelist ) RETURN CALCULATE( MAX (Location[Location] ), FILTER ( Location, Location[Sales Code] IN _others ) )

Please help adapt code to use with separate tables.