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.
Community ChampionTo achieve the desired outcome in Power BI using DAX, you can follow these steps:
- Import your data into Power BI as a table.
- Create a new table to generate a list of unique dates.
- Create relationships between your original table and the date table based on the date field.
- Use DAX measures to calculate the average score for each user_hash up to the current date.
Here's how you can write the DAX measures:
- Calculate the cumulative sum of scores for each user_hash:
CumulativeScore =
VAR CurrentDate = MAX('Date'[Date])
RETURN
CALCULATE(
AVERAGE('YourTable'[Score]),
FILTER(
ALL('Date'),
'Date'[Date] <= CurrentDate
)
)
Use the CumulativeScore measure to populate the Score_hist column in your final table visualization:
Score_hist =
VAR CurrentUserHash = 'YourTable'[User_hash]
VAR CurrentDate = MAX('Date'[Date])
RETURN
CALCULATE(
[CumulativeScore],
FILTER(
ALL('YourTable'),
'YourTable'[User_hash] = CurrentUserHash &&
'YourTable'[Date] <= CurrentDate
)
)
Once you have created these measures, you can create a new table visualization in Power BI. Place the Date column from your date table and the User_hash column from your original table into the Rows field, and then add the Score_hist measure to the Values field.
This should give you a table with the desired output, where each user_hash has a score value for each day, even if they were not present on that day. The score is the average of all previous days.
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.
Pan_ForexThank you very much for your help. I did everything as instructed but I have a problem with the Score_hist measure. I can't denominate CurrentUserHash and refer to my main table in it.
- 123abc
I see. It seems like you want to calculate the historical average score for each user_hash up to the current date. Let me adjust the measure to include the reference to the current user_hash in the main table. Here's how you can modify the measure:
Score_hist =
VAR CurrentDate = MAX('DateTable'[Date])
VAR CurrentUserHash = SELECTEDVALUE('OriginalData'[User_hash])
RETURN
CALCULATE(
AVERAGE('OriginalData'[Score]),
FILTER(
ALL('OriginalData'),
'OriginalData'[Date] <= CurrentDate &&
'OriginalData'[User_hash] = CurrentUserHash
)
)This measure calculates the historical average score for the currently selected user_hash up to the current date. It filters the OriginalData table to include only rows where the Date is less than or equal to the current date and where the User_hash matches the selected user_hash.
Make sure to replace 'OriginalData' with the name of your main table if it's different.
You can then use this measure in your Power BI visualizations alongside the Date and User_hash fields to display the historical average score for each user_hash on each date.
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.
- Pan_Forex
Thank you for your reply. Now the measure works almost correctly. The problem is that there are days when there is no player data. If I create an animated chart then the given player will disappear from the chart on days when he is not there.