VAR Loses Drillthrough Filter After Using ALL

[Tiago01oliveira]

The title may be a bit confusing but i have 2 pages, a club page that drills through to a player page with more information, this drill through uses Player ID, from table Player Details. I then have a measure (Selected Player ShotsOnTarget)that captures the shots on target for that specific player simply using Sum('Shooting GW'[Shots on Target]), then using a separate table (Graph Table) i have all players and their shots on target (SOT Similarity) which allows the comparison to find players with similar shots on target and ignore the drillthrough filter. 

Similar Players Metric =

VAR SelectedPlayerShots = [Selected Player ShotsOnTarget]

VAR LowerBound = SelectedPlayerShots * 0.9

VAR UpperBound = SelectedPlayerShots * 1.1

RETURN

CALCULATE(

IF(

AND(

SUM('Graph Table'[SOT  Similarity]) >= LowerBound,

SUM('Graph Table'[SOT  Similarity]) <= UpperBound

),

1,

VALUE(SelectedPlayerShots)

),

ALL('Player Details'[Player],'Player Details'[ID])

)

this metric is meant to compare the selected value measure and all the players in the graph table and their shots on target and if they are within 10 percent it should return 1 else 0, however the issue is with the results, it seems to return 1 for players with 0 shots on target instead of the actual range which should be 2.7 to 3.3 for the example selected player. after inspection the Selected Player ShotsOnTarget Measure is clearly blank but only in the similar player metrics measure I assume because of the ALL() used at the bottom since on its own its fine. but i'm not sure how to fix this. this does however need to be dynamic just because there are 580 players so the ID cant be Hardcoded

Sorry for the long post and any thing i can add i will try my best 

[vgarciasouza]

I believe the answer is to use ALLEXCEPT.

Perhaps is:

ALLEXCEPT('Player Details', [Player])

[Tiago01oliveira]

I changed the All to ALLEXCEPT and it still has the same effect unfortunately

[v-xinruzhu-msft]

Hi @Tiago01oliveira 

Try the following measure.

Similar Players Metric =
VAR SelectedPlayerShots = [Selected Player ShotsOnTarget]
VAR LowerBound = SelectedPlayerShots * 0.9
VAR UpperBound = SelectedPlayerShots * 1.1
RETURN
    CALCULATE (
        IF (
            AND (
                SUM ( 'Graph Table'[SOT  Similarity] ) >= LowerBound,
                SUM ( 'Graph Table'[SOT  Similarity] ) <= UpperBound
            ),
            1,
            VALUE ( SelectedPlayerShots )
        ),
        ALLSELECTED ( 'Player Details' ),
        'Player Details'[ID] IN VALUES ( 'Player Details'[ID] )
    )

Best Regards!

Yolo Zhu

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