Slicer Filtering the multi selected values search exact value

[amitkhare82]

Hello All,

I'm running into something that may be a very common issue but cannot get it through.

Here is my situation.

1.  A Slicer table- Device

Device ID	DeviceName
1	New Device
2	Pre owned Device
3	Refurbished Device
4	Outdated Device

2. Data table

Projects	Related Devices
Project 1	New Device, Pre owned Device
Project 2	Pre owned Device, Outdated Device, New Device
Project 3	Refurbished Device
Project 4	Outdated Device, Refurbished Device
Project 5	Pre owned Device
Project 6	New Device

My slicer is multiselected so I select " New Device" I should be getting 3 rows as a result in table but I get only one record which Project 6. What I'm looking for when I select the New Device from slicer, it should give the result of Project1, Project 2and Project6. If I select the New Device and Pre owned device, I should be getting the Project 1, 2, 5 and 6.

Tried using the custom measure but it doesn' work.
IsFiltered = IF(
SUMX('Device',
FIND(
'Slicer'[DeviceName],
MAX(Projects[RelatedDevices]),,0)
) > 0,
"True",
"False"
)

Any suggestions would be greatly appreciated.

Thanks,

[v-ljerr-msft]

Hi @amitkhare82,

Based on my test, you should be able to use the formula below to create a new measure, then use it to apply a visual level filter(IsFiltered is 1) on the Table visual which shows the projects to get the expected result in your scenario.

IsFiltered = 
IF (
    SUMX (
        'Device',
        FIND ( 'Device'[DeviceName], MAX ( Projects[Related Devices] ),, 0 )
    )
        > 0,
    1,
    0
)

Note: make sure there is no any relationship between the two table.

Here is sample pbix file for your reference. 

Regards

[amitkhare82]

Thank you @v-ljerr-msft

Yes, My formula works with a limited set of data. I sent the samples of data but in my thousands of records, My Device Name slicer shows the Blank too. If I select two values from filter , it doesn't filter the correct records but If I select the Blank along with other values (one or more )it works with the same Measured column. I tried to hide the Blank in filter but it didn’t work either. 

I suspect that due to some or many records having the blank values of related devices, the formula based "Measured column" doesn't work the way it would work. Although my Device Table doesn't have any blank record.

Thank again for looking into this.