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

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?