Forum Discussion
Display latest data compared to earliest data
Hey AngelaB ,
You're off to a great start already! Your approach of identifying the baseline and latest timepoints is logical and works well, especially for someone new to Power BI and DAX. That said, your solution can be improved slightly for performance, readability, and maintainability.
DAX for Include in Graph
Include in graph = VAR CurrentLocation = 'Table'[Location] VAR CurrentTimepoint = 'Table'[Timepoint] VAR MaxTimepoint = CALCULATE( MAX('Table'[Timepoint]), ALLEXCEPT('Table', 'Table'[Location]) ) RETURN SWITCH( TRUE(), CurrentTimepoint = 1, 1, -- Baseline CurrentTimepoint = MaxTimepoint, 2, -- Latest -1 -- Other )Visual Setup Tips:
Use this column (Include in graph) as a visual-level filter, keeping only values 1 (baseline) and 2 (latest).
You can also create a new column or measure to label the timepoints like 'Baseline', 'Latest', and use that in a legend or axis for clarity.
If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.
Best Regards,
Nasif Azam
Hi AngelaB ,
Wanted to start off by saying this is impressive for a novice, Kudos to you!
What I would recommend is turning these into measures rather than calculated columns. Using measures instead of calculated columns is generally a better approach in Power BI for this type of analysis because measures are dynamic and they respond to user interactions, slicers, and filters in real time. This means your logic for identifying the baseline and latest timepoints will always adapt to the current context of the report, without needing to persist static values. Measures also perform better because they are computed on the fly and don't consume additional storage in your data model like calculated columns do.
Here are the columns rewritten as measures:
Max timepoint for location =
CALCULATE(
MAX('Table'[Timepoint]),
ALLEXCEPT('Table', 'Table'[Location])
)Include in graph =
SWITCH(
TRUE(),
'Table'[Timepoint] = 1, 1,
'Table'[Timepoint] = [Max timepoint for location], 2,
-1
)
Another way to optimize your DAX is using the SWITCH() function instead of the nested IF() statements in your Include in graph DAX. The SWITCH() function allows you to add multiple conditions in a clean and easy to read format. I've modified the DAX in the measure above as an example.
If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.
Thanks,
Samson
Thanks for taking the time to look over this and provide a suggested solution SamsonTruong . I did try something along these lines originally but couldn't get the measures to work so defaulted back to columns. I can get the first measure to work fine (Max timepoint for location), but then not the Include in graph measure... the error I'm receiving is that "A single value for column" cannot be determined, presumably because this is line-by-line/raw data and not summarised/aggregate data so has multiple entries per location/timepoint (apologies this may have been due to a poor initial description on my part).
Hi AngelaB , No worries at all. Your asummption of why this error is occuring is correct due to the multiple entries per location/timepoint. A way around this error is to apply an aggregation around that field such as MAX() for example. However, I would advise to be cautious of using this as depending on your desired result, this could alter the logic of your measure.
Thank you SamsonTruong ! I will have a play and see if I can get those to work for me.
