Forum Discussion
Helper IIIdata modeling for chart history
To achieve the behavior where the score from the previous day is displayed when there is no score available for a player on a particular day, you can adjust your DAX measure accordingly. You'll need to find the last available score for each player and propagate it forward.
Here's how you can modify your Score_hist measure to achieve this:
Score_hist =
VAR CurrentDate = SELECTEDVALUE('public random_players'[Data_random])
VAR CurrentUserHash = SELECTEDVALUE('public random_players'[user_nickname])-- Find the last available score for the current user hash before or on the current date
VAR LastScore =
CALCULATE(
MAX('public random_players'[Score_date_player]),
FILTER(
ALL('public random_players'),
'public random_players'[Data_random] <= CurrentDate &&
'public random_players'[user_nickname] = CurrentUserHash &&
'public random_players'[Score_date_player] <> BLANK()
)
)-- If no last score is found, return BLANK; otherwise, return the last score
RETURN
IF(
ISBLANK(LastScore),
BLANK(),
LastScore
)In this measure:
- We find the last available score for the current user hash before or on the current date using the LastScore variable.
- Then, we use an IF statement to check if a last score is found. If a last score is found, we return it; otherwise, we return BLANK.
This measure should provide the behavior you described, where if a player received a score on a previous day, that score will be assigned to the player for subsequent days until a new score is recorded.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
Helper IIIThank you for your reply. Everything is correct except that instead of BLANK score it should display the score from the previous day when the player last received a score. If someone received a score on day 1 and, for example, 9 then on days 2-8 he should be assigned a score from day 1.
Community ChampionTo achieve the behavior where the score from the previous day is displayed when there is no score available for a player on a particular day, you can adjust your DAX measure accordingly. You'll need to find the last available score for each player and propagate it forward.
Here's how you can modify your Score_hist measure to achieve this:
Score_hist =
VAR CurrentDate = SELECTEDVALUE('public random_players'[Data_random])
VAR CurrentUserHash = SELECTEDVALUE('public random_players'[user_nickname])
-- Find the last available score for the current user hash before or on the current date
VAR LastScore =
CALCULATE(
MAX('public random_players'[Score_date_player]),
FILTER(
ALL('public random_players'),
'public random_players'[Data_random] <= CurrentDate &&
'public random_players'[user_nickname] = CurrentUserHash &&
'public random_players'[Score_date_player] <> BLANK()
)
)
-- If no last score is found, return BLANK; otherwise, return the last score
RETURN
IF(
ISBLANK(LastScore),
BLANK(),
LastScore
)
In this measure:
- We find the last available score for the current user hash before or on the current date using the LastScore variable.
- Then, we use an IF statement to check if a last score is found. If a last score is found, we return it; otherwise, we return BLANK.
This measure should provide the behavior you described, where if a player received a score on a previous day, that score will be assigned to the player for subsequent days until a new score is recorded.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.