Forum Discussion

py1029's avatar
py1029
Frequent Visitor
4 years ago
Solved

Can I remove the overlapping values in different listing after applying filter?

Hi,

 

I would like to ask if there is a possibility to remove the overlapping project appear in my table after I apply different "Plant" and "Distance from Plant (KM)" filter? 

 

For example, I have a sample data with these columns:

 

In Power BI, I have these filters to get my desired output:

Below is my current outcome and the expected outcome that I would like to get:

 

 

In expected outcome, the project would not appear in another plant listing by calculating the shortest distance from plant. When I apply the filter, projects which are within 5 km distance from two plants will only appear in one of the plant listing by calculating the shorter distance of the project to the plant. 

 

Is there a possible way to come out with this expected outcome? 

 

Thanks

Py

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi py1029 

    Here I suggest you to create a measure to filter your table visual by Slicer from unrelated table, Distance and Plant Name.

    Firstly, we need to create two unrelated table Distance and Plant Name.

    Distance from Plant(KM) = GENERATESERIES(0, 10, 1)
    Plant Name = VALUES('Table'[Plant Name])

    Measure:

    Filter =
VAR _CURRENTDistance =
    SUM ( 'Table'[Distance from Plant(KM)] )
VAR _CURRENTPlantName =
    MAX ( 'Table'[Plant Name] )
VAR _SelectPlantName =
    SELECTEDVALUE ( 'Plant Name'[Plant Name] )
VAR _MinDistance =
    MIN ( 'Distance from Plant(KM)'[Parameter] )
VAR _MaxDistance =
    MAX ( 'Distance from Plant(KM)'[Parameter] )
VAR _MinDistanceEachProject =
    MINX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Project Name] = MAX ( 'Table'[Project Name] )
        ),
        'Table'[Distance from Plant(KM)]
    )
RETURN
    IF (
        ISFILTERED ( 'Plant Name'[Plant Name] ),
        IF (
            _CURRENTPlantName = _SelectPlantName
                && _CURRENTDistance > _MinDistance
                && _CURRENTDistance < _MaxDistance
                && _CURRENTDistance = _MinDistanceEachProject,
            1,
            0
        ),
        1
    )

    Create a table visual, add this measure into filter field in this visual and set it to show items if value =1.

    Select Sentul:

    Select KLC:

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi py1029 

    I think you want to filter you visual by this unrelated Distance from Plant(KM). You can refer to [Filter] measure to create a new measure to to filter other visuals by Distance, in range return 1 and out range return 0. Then add this new measure into filter field in other visuals and set this measure to show items when value =1.

    Measure:

    Basic Filter = 
VAR _SelectPlant = VALUES('Plant Name'[Plant Name])
VAR _MINDistance = MIN('Distance from Plant(KM)'[Parameter])
VAR _MAXDistance = MAX('Distance from Plant(KM)'[Parameter])
RETURN
IF(MAX('Table'[Plant Name]) IN _SelectPlant && SUM('Table'[Distance from Plant(KM)])>=_MINDistance&&SUM('Table'[Distance from Plant(KM)])<=_MAXDistance,1,0)

    Result:

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    It's possible but rather tricky. Somehow each table not only needs to be filtered but also know how the other table is filtered so that it can act accordingly.

     

    You might need to set up two independent tables as slicers for your plants and then check that each project in the table is the minimal distance among the two selected plants. If you have exactly two plants, then this might not be necessary.

    • py1029's avatar
      py1029
      Frequent Visitor
      4 years ago

      Hi AlexisOlson 

       

      I have 9 plants in total so I need to create 9 tables to check the distance of projects from all these 9 plants?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi py1029 

        Here I suggest you to create a measure to filter your table visual by Slicer from unrelated table, Distance and Plant Name.

        Firstly, we need to create two unrelated table Distance and Plant Name.

        Distance from Plant(KM) = GENERATESERIES(0, 10, 1)
        Plant Name = VALUES('Table'[Plant Name])

        Measure:

        Filter =
VAR _CURRENTDistance =
    SUM ( 'Table'[Distance from Plant(KM)] )
VAR _CURRENTPlantName =
    MAX ( 'Table'[Plant Name] )
VAR _SelectPlantName =
    SELECTEDVALUE ( 'Plant Name'[Plant Name] )
VAR _MinDistance =
    MIN ( 'Distance from Plant(KM)'[Parameter] )
VAR _MaxDistance =
    MAX ( 'Distance from Plant(KM)'[Parameter] )
VAR _MinDistanceEachProject =
    MINX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Project Name] = MAX ( 'Table'[Project Name] )
        ),
        'Table'[Distance from Plant(KM)]
    )
RETURN
    IF (
        ISFILTERED ( 'Plant Name'[Plant Name] ),
        IF (
            _CURRENTPlantName = _SelectPlantName
                && _CURRENTDistance > _MinDistance
                && _CURRENTDistance < _MaxDistance
                && _CURRENTDistance = _MinDistanceEachProject,
            1,
            0
        ),
        1
    )

        Create a table visual, add this measure into filter field in this visual and set it to show items if value =1.

        Select Sentul:

        Select KLC:

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.