Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Return values for blanks

Hello everyone, 

 

I am comparing statistics from Messi and Ronaldo. I have column with Date, Player and Goals. And I created a cumulative measure, which is as follows:

 

Cumulative goals = 
CALCULATE (
SUM ( 'Stats'[Goals] ),
FILTER (
ALL ( 'Stats' ),
'Stats'[Date] <= MAX ( 'Stats'[Date] )
&& 'Stats'[Player] = MAX ( 'Stats'[Player] )
))

The result is show in the left table:


I am looking for output as in the right column. How can I change my measure to get to output with '0' for the one when the other one scored?



Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    According to your description, here's my solution.

    1.Create a new table.

    Table =
GENERATE (
    VALUES ( Stats[Date] ),
    UNION ( ROW ( "Player", "Messi" ), ROW ( "Player", "Ronaldo" ) )
)

    2.Create a calculated column in the new table.

    Goal =
IF (
    MAXX (
        FILTER (
            ALL ( 'Stats' ),
            'Stats'[Date] = EARLIER ( 'Table'[Date] )
                && 'Stats'[Player] = EARLIER ( 'Table'[Player] )
        ),
        'Stats'[Goals]
    )
        <> BLANK (),
    MAXX (
        FILTER (
            ALL ( 'Stats' ),
            'Stats'[Date] = EARLIER ( 'Table'[Date] )
                && 'Stats'[Player] = EARLIER ( 'Table'[Player] )
        ),
        'Stats'[Goals]
    ),
    0
)

    3.Create a measure.

    Cumulative goals =
SUMX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Date] <= MAX ( 'Table'[Date] )
            && 'Table'[Player] = MAX ( 'Table'[Player] )
    ),
    'Table'[Goal]
)

    Get the expected result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

6 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    According to your description, here's my solution.

    1.Create a new table.

    Table =
GENERATE (
    VALUES ( Stats[Date] ),
    UNION ( ROW ( "Player", "Messi" ), ROW ( "Player", "Ronaldo" ) )
)

    2.Create a calculated column in the new table.

    Goal =
IF (
    MAXX (
        FILTER (
            ALL ( 'Stats' ),
            'Stats'[Date] = EARLIER ( 'Table'[Date] )
                && 'Stats'[Player] = EARLIER ( 'Table'[Player] )
        ),
        'Stats'[Goals]
    )
        <> BLANK (),
    MAXX (
        FILTER (
            ALL ( 'Stats' ),
            'Stats'[Date] = EARLIER ( 'Table'[Date] )
                && 'Stats'[Player] = EARLIER ( 'Table'[Player] )
        ),
        'Stats'[Goals]
    ),
    0
)

    3.Create a measure.

    Cumulative goals =
SUMX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Date] <= MAX ( 'Table'[Date] )
            && 'Table'[Player] = MAX ( 'Table'[Player] )
    ),
    'Table'[Goal]
)

    Get the expected result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Try with +0

     

    Cumulative goals =
    CALCULATE (
    SUM ( 'Stats'[Goals] ),
    FILTER (
    ALL ( 'Stats' ),
    'Stats'[Date] <= MAX ( 'Stats'[Date] )
    && 'Stats'[Player] = MAX ( 'Stats'[Player] )
    ))+0

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      amitchandak Thank you, and that would work, but in this case, it doesn't. 

      I think it's because there's only one day per player: 

      When Ronaldo scores and Messi doesn't, you have a row with the specific date, the number of goals and Ronaldo. 

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        Anonymous , do you have a Separate date and player table ? In that case this might work

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    amitchandak I don't, but I can create a date table. It will be a one-to-one relationship between the date column in my Stats table to the Date[date]. So, I am not sure if that work, but the Date table isn't linked to a specific player, if you're aiming for that.