topic Re: data modeling for chart history in DAX Commands and Tips

data modeling for chart history

Pan_Forex — Mon, 19 Feb 2024 16:28:15 GMT

Hello, I would like to edit and later export the data in csv, which will allow to do an effect like in this video: https://www.youtube.com/watch?v=4-2nqd6-ZXg

I created a new table with a list of unique dates from to. In practice, I would like each [userhash] to have a [score] value on each day even if it was not present on that day(you can assign the last value). [Score] is the average of all previous days.

Small sample:

Date	User_hash	Score
2023-01-01	uyuytrtryfgh	7
2023-01-01	asdasdqwecx	6
2023-01-02	asdasdqwecx	8
2023-01-03	asdasdqwecx	5
2023-01-04	asdasdqwecx	9
2023-01-05	asdasdqwecx	11
2023-01-06	asdasdqwecx	8
2023-01-07	asdasdqwecx	1
2023-01-08	asdasdqwecx	2
2023-01-09	uyuytrtryfgh	9
2023-01-09	asdasdqwecx	10
2023-01-10	juurtgfdvw	4

Output:

Date	User_hash	Score_hist
2023-01-01	uyuytrtryfgh	7
2023-01-01	asdasdqwecx	6
2023-01-02	uyuytrtryfgh	7
2023-01-02	asdasdqwecx	7
2023-01-03	uyuytrtryfgh	7
2023-01-03	asdasdqwecx	6.33
2023-01-04	uyuytrtryfgh	7
2023-01-04	asdasdqwecx	7
2023-01-05	uyuytrtryfgh	7
2023-01-05	asdasdqwecx	7.8
2023-01-06	uyuytrtryfgh	7
2023-01-06	asdasdqwecx	7.83
2023-01-07	uyuytrtryfgh	7
2023-01-07	asdasdqwecx	6.86
2023-01-08	uyuytrtryfgh	7
2023-01-08	asdasdqwecx	6.25
2023-01-09	uyuytrtryfgh	8
2023-01-09	asdasdqwecx	6.66
2023-01-10	asdasdqwecx	6.66
2023-01-10	uyuytrtryfgh	8
2023-01-10	juurtgfdvw	4

Re: data modeling for chart history

123abc — Tue, 20 Feb 2024 05:48:13 GMT

To 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.

Re: data modeling for chart history

Pan_Forex — Tue, 20 Feb 2024 15:16:48 GMT

Thank 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.

Re: data modeling for chart history

123abc — Wed, 21 Feb 2024 05:04:31 GMT

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.

Re: data modeling for chart history

Pan_Forex — Wed, 21 Feb 2024 13:50:35 GMT

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.

Re: data modeling for chart history

123abc — Thu, 22 Feb 2024 09:48:55 GMT

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.

Re: data modeling for chart history

Pan_Forex — Thu, 22 Feb 2024 13:31:58 GMT

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.

Re: data modeling for chart history

Pan_Forex — Thu, 22 Feb 2024 15:55:48 GMT

I've managed to do it this way so far(I assigned all players to each date(I omitted all conditions to make it simpler for me):

New_Table =

VAR AllCombinations =

CROSSJOIN(

DISTINCT('public random_players'[Data_random]),

DISTINCT('public random_players'[user_nickname])

)

VAR ActualData =

SELECTCOLUMNS(

FILTER(

'public random_players',

'public random_players'[Data_random] = SELECTCOLUMNS(AllCombinations, "Date", [Data_random]) &&

'public random_players'[user_nickname] = SELECTCOLUMNS(AllCombinations, "Name", [user_nickname])

"Date", 'public random_players'[Data_random],

"Name", 'public random_players'[user_nickname]

)

RETURN

SELECTCOLUMNS(

AllCombinations,

"Date", [Data_random],

"Name", [user_nickname]

)

In the new [Value] column, I calculated the score for each player with the date condition. [score_date_player] is the average in the public random_players table, which calculates the average of the previous days only.

Value =
IF(
ISBLANK(
LOOKUPVALUE(
'public random_players'[Score_date_player],
'public random_players'[user_nickname],
NowaTabela[Name]
)
),
CALCULATE(
MAX('public random_players'[Score_date_player]),
FILTER(
'public random_players',
'public random_players'[user_nickname] = NowaTabela[Name] &&
'public random_players'[data_random] < EARLIER('NowaTabela'[Date])
)
),
LOOKUPVALUE(
'public random_players'[Score_date_player],
'public random_players'[user_nickname],
NowaTabela[Name]
)
)

The problem is that Value returns correct values and a BLANK values if there is no data for specific date. How do I edit my function to return the last seen score_date_player instead of blank?

Re: data modeling for chart history

123abc — Mon, 26 Feb 2024 10:24:59 GMT

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.