Forum Discussion

Jay_AVA's avatar
Jay_AVA
Frequent Visitor
3 years ago
Solved

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

  • Anonymous's avatar
    Anonymous
    3 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

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion

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

    • Jay_AVA's avatar
      Jay_AVA
      Frequent Visitor

      Hi PaulDBrown 

       

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

       

      Thanks!

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion

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