Forum Discussion
Apply Filter Based On Selected Values
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!!!
- Anonymous3 years ago
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
6 Replies
- PaulDBrownCommunity Champion
Can you share some actual sample dummy data or a link to a dummy PBIX file?
- Jay_AVAFrequent Visitor
- PaulDBrownCommunity Champion
How do you define "close"? Is it if the difference between the selected "line" and other lines <= 1?