Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Tiago01oliveira
New Member

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 IMG_0130.jpeg

 

IMG_0131.jpeg

 

3 REPLIES 3
vgarciasouza
Frequent Visitor

I believe the answer is to use ALLEXCEPT.

 

Perhaps is:

ALLEXCEPT('Player Details', [Player])

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

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.