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       ...
Need Help
  • 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

PaulDBrown's avatar
PaulDBrown
Community Champion
3 years ago

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

Jay_AVA's avatar
Jay_AVA
Frequent Visitor
3 years ago

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
    3 years ago

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

    • Jay_AVA's avatar
      Jay_AVA
      Frequent Visitor
      3 years ago

      Hi PaulDBrown 

       

      PaulDBrown wrote:

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

      Yes, exactly. 

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