Forum Discussion

Tiago01oliveira's avatar
Tiago01oliveira
New Member
2 years ago

VAR Loses Drillthrough Filter After Using ALL

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 

 

 

Need Help

3 Replies

  • vgarciasouza's avatar
    vgarciasouza
    Frequent Visitor
    2 years ago

    I believe the answer is to use ALLEXCEPT.

     

    Perhaps is:

    ALLEXCEPT('Player Details', [Player])

    • Tiago01oliveira's avatar
      Tiago01oliveira
      New Member
      2 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.