Return Items that have Neither Item Selected

[BrianNeedsHelp]

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

[Kedar_Pande]

@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

[Jihwan_Kim · Accepted solution]

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

[BrianNeedsHelp]

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 

[Jihwan_Kim]

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.

[BrianNeedsHelp]

@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.   

[BrianNeedsHelp]

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