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

[py1029]

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

[Anonymous · Accepted solution]

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.

[AlexisOlson]

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]

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 · Accepted solution]

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.

[py1029]

Hi @Anonymous 

Thanks for your help in solving the issue. I manage to come out the solution with the measure. Currently I'm facing another issue. When I want to do a multiple selection of the plant, I got a problem where some of the projects gone missing. 

When I single select the plant, I manage to get the projects by shortest distance.

However, when I want to have the multiple selection, projects that are within 5km from both plants are not showing, only projects that are within 5km from either one of the plant will be showing. 

Is there any way to fix these based on the measure? Your help is much appreciated!

Thanks

[py1029]

Hi @Anonymous 

Thank you for your reply. I found that if I use the unrelated table, Distance from plant (km) as a slicer, it can't filter the table. For e.g. when I change the distance from plant (km) to 10km, the projects didn't change. 

Or do I need to use back the Distance from plant (km) from my previous table as the slicer?

[Anonymous · Accepted solution]

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.