Forum Discussion

Pan_Forex's avatar
Pan_Forex
Icon for Helper III rankHelper III
2 years ago
Solved

data modeling for chart history

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-01uyuytrtryfgh7
2023-01-01asdasdqwecx6
2023-01-02asdasdqwecx8
2023-01-03asdasdqwecx5
2023-01-04asdasdqwecx9
2023-01-05asdasdqwecx11
2023-01-06asdasdqwecx8
2023-01-07asdasdqwecx1
2023-01-08asdasdqwecx2
2023-01-09uyuytrtryfgh9
2023-01-09asdasdqwecx10
2023-01-10juurtgfdvw4

 

Output:

Date                    User_hash               Score_hist
2023-01-01uyuytrtryfgh7
2023-01-01asdasdqwecx6
2023-01-02uyuytrtryfgh7
2023-01-02asdasdqwecx7
2023-01-03uyuytrtryfgh7
2023-01-03asdasdqwecx6.33
2023-01-04uyuytrtryfgh7
2023-01-04asdasdqwecx7
2023-01-05uyuytrtryfgh7
2023-01-05asdasdqwecx7.8
2023-01-06uyuytrtryfgh7
2023-01-06asdasdqwecx7.83
2023-01-07uyuytrtryfgh7
2023-01-07asdasdqwecx6.86
2023-01-08uyuytrtryfgh7
2023-01-08asdasdqwecx6.25
2023-01-09uyuytrtryfgh8
2023-01-09asdasdqwecx6.66
2023-01-10asdasdqwecx6.66
2023-01-10uyuytrtryfgh8
2023-01-10juurtgfdvw4
  • 123abc's avatar
    123abc
    2 years ago

    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.

8 Replies

  • 123abc's avatar
    123abc
    Icon for Community Champion rankCommunity Champion
    2 years ago

    To achieve the desired outcome in Power BI using DAX, you can follow these steps:

    1. Import your data into Power BI as a table.
    2. Create a new table to generate a list of unique dates.
    3. Create relationships between your original table and the date table based on the date field.
    4. 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:

    1. 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_Forex's avatar
      Pan_Forex
      Icon for Helper III rankHelper III
      2 years ago

      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. 

      • 123abc's avatar
        123abc
        Icon for Community Champion rankCommunity Champion
        2 years ago

        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's avatar
    Pan_Forex
    Icon for Helper III rankHelper III
    2 years ago

    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?