Apply Filter Based On Selected Values

[Jay_AVA]

Hi everyone,

I have 2 tables: 

Product

Name	Home Location
A	02-1
B	03-1

Location

Location ID	Status	Line Number	Level
01-1	Empty	1	1
01-2	Empty	1	2
02-1	Full	2	1
02-2	Empty	2	2
03-1	Full	3	1
03-2	Empty	3	2
04-1	Empty	4	1
04-2	Empty	4	2

Each product has an unique location. And there are a lot of locations in the warehouse.

Does any one know, is there a way that I can show all the closest empty locations (difference <= 1) when user choose a product? 

For instance, product A is in line 2 level 1. When I choose product A, the location table will only show empty locations in line 1, line 2, and line 3 (01-1, 01-2, 02-2, 03-2) and ignore line 4's location.

Any thoughts would be appreciated!! Thanks!!!

[PaulDBrown]

Can you share some actual sample dummy data or a link to a dummy PBIX file?

[Jay_AVA]

Hi @PaulDBrown 

It doesn't allow me to share PBIX file here. So I reposted my sample data. 

Thanks!

[PaulDBrown]

How do you define "close"? Is it if the difference between the selected "line" and other lines <= 1?

[Jay_AVA]

Hi @PaulDBrown 

---

@PaulDBrown wrote:

Is it if the difference between the selected "line" and other lines <= 1?

---

Yes, exactly. 

[Anonymous · Accepted solution]

Hi @Jay_AVA ,

You can follow the steps below to get it, please find the details in the attachment.

1. Create a measure as below:

Flag = 
VAR _sellid =
    SELECTEDVALUE ( 'Location'[Location ID] )
VAR _selname =
    SELECTEDVALUE ( 'Product'[Name] )
VAR _hlocation =
    CALCULATE (
        MAX ( 'Product'[Home Location] ),
        FILTER ( ALLSELECTED ( 'Product' ), 'Product'[Name] = _selname )
    )
VAR _linenum =
    CALCULATE (
        MAX ( 'Location'[Line Number] ),
        FILTER ( ALLSELECTED ( 'Location' ), 'Location'[Location ID] = _hlocation )
    )
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'Location'[Location ID] ),
        FILTER (
            ALLSELECTED ( 'Location' ),
            'Location'[Line Number] <= _linenum + 1
                && 'Location'[Status] = "Empty"
        )
    )
RETURN
    IF ( _sellid IN _tab, 1, 0 )

2. Create a table visual and apply the visual-level filter with the condition(Flag is 1)

Best Regards

[Jay_AVA]

Thanks @Anonymous 

It works!! Appreciate your help!!

I just added one more condition to the last filter since I only need the closest location.

FILTER (

            ALLSELECTED ( 'Location' ),

            'Location'[Line Number] <= _linenum + 1

                && 'Location'[Line Number] >= _linenum - 1

                && 'Location'[Status] = "Empty"