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 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.
Community ChampionI 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.
- 123abc
To ensure that the players remain visible on days when there is no data for them, you need to make sure that your data model includes all the players for all dates, even if they don't have any associated data for certain days. This means you need to ensure that your calendar table includes all dates and is properly related to your main data table.
Here are the steps you can take to ensure the players remain visible on the chart:
Calendar Table: Make sure your calendar table includes all the dates you want to visualize on your chart. This table should cover a range of dates that includes the earliest date in your data and the latest date you want to display.
Relationships: Ensure there is a relationship between your calendar table and your main data table. This relationship should be based on the date column present in both tables.
Update DAX Calculation: Adjust your DAX calculation to consider all dates and all players, even if there is no data available for certain combinations of dates and players.
Here's an updated version of the DAX calculation for Score_hist that considers all dates and all players:
Score_hist =
VAR CurrentDate = 'Calendar'[Date]
VAR CurrentUserHash = 'YourTable'[User_hash]RETURN
IF (
ISBLANK ( CurrentUserHash ),
BLANK (),
CALCULATE (
AVERAGE ( 'YourTable'[Score] ),
FILTER (
'YourTable',
'YourTable'[Date] <= CurrentDate &&
'YourTable'[User_hash] = CurrentUserHash
)
)
)This modification ensures that even if there's no data available for a particular player on a certain date, the player will still be included in the visualization with a blank score value. This way, the player will remain visible on the chart throughout the date range you're visualizing.
Make sure to adjust the DAX expression according to your actual table and column names in Power BI. With these adjustments, your animated chart should display all players consistently across all dates.
If I answered your question, please mark my post as solution, Appreciate your Kudos.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
- Pan_Forex
Thank 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.