Power BI DAX Count Winners from a subset

RWrightHvs ,

 

 

To calculate the number of days where Player X (selected from Player 1) had a lower position than Player Y (selected from Player 2), we can use a DAX measure. Here's the approach to achieve this:

DAX Measure for Wins

We will compare the positions of the two selected players for each day and count the days where Player X's position is lower than Player Y's.

Steps:

1. Filter the data to only include the two selected players.
2. Compare their positions for each day.
3. Count the days where Player X's position is lower than Player Y.

Here's the measure:

Count of Wins = 
VAR Player1ID = SELECTEDVALUE('Player 1'[User ID])
VAR Player2ID = SELECTEDVALUE('Player 2'[User ID])

-- Filter game data to only include the selected players
VAR FilteredData = 
    FILTER(
        'Gamedata', 
        'Gamedata'[User ID] = Player1ID || 'Gamedata'[User ID] = Player2ID
    )

-- Group by Date and compare positions
VAR Wins =
    SUMX(
        SUMMARIZE(
            FilteredData,
            'Gamedata'[Date],
            "Player1Position", 
                MAXX(FILTER(FilteredData, 'Gamedata'[User ID] = Player1ID), 'Gamedata'[Position]),
            "Player2Position", 
                MAXX(FILTER(FilteredData, 'Gamedata'[User ID] = Player2ID), 'Gamedata'[Position])
        ),
        IF([Player1Position] < [Player2Position], 1, 0)
    )

RETURN Wins

• This measure assumes that both players participate on the same days. If there are dates where only one player competes, ensure your data handling accounts for these cases by adding appropriate filters or conditions.
• The measure is dynamic and updates based on the players selected in your slicers.

Result

You can use this measure in a card visual or any other visualization to display the count of days where Player X had a lower position than Player Y.

 

Best regards,