Display latest data compared to earliest data

[AngelaB]

Hello Fabric Community

I'm very much a novice and trying to learn how best to approach solutions in Power BI so would appreciate some guidance! I have a database that includes individual records (rows) that are summarised by location and a categorical timepoint (baseline, year 1, year 2 etc). Locations have come on board at different times and are therefore at different timepoints.

I want to create a visual that compares baseline data to latest data across locations, so needs to align all of the latest timepoints. To achieve this I've created a new table column [Max timepoint by location] which identifies what the maximum timepoint for each location is and inputs this value into the row.

Max timepoint for location = MAXX(FILTER('Table','Table'[Location]= EARLIER ( 'Table'[Location] )), 'Table'[Timepoint])

I've then created another table column [Include in graph] to use as a 'filter' which assigns a value of 1 for 'baseline', 2 for 'latest data' and -1 for all other data. I've then filtered the visual based on this.

Include in graph = IF('Table'[Timepoint] = 1, 1, IF('Table'[Timepoint] = 'Table'[Max timepoint for ward], 2, -1))

See basic example of how this data looks below. While this works, surely there is a better way to approach the problem? Perhaps by defining variables within the DAX? How would you do it?

Location	Timepoint	Max Timepoint for Location	Include in graph
A	1	3	1
A	2	3	-1
A	3	3	2
B	1	4	1
B	2	4	-1
B	3	4	-1
B	4	4	2
C	1	2	1
C	2	2	2

[Nasif_Azam · Accepted solution]

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

[AngelaB]

Yes, thank you, this works and is a more elegant solution to my clunky multiple columns! Thank you for taking the time to review this for me.

[AngelaB]

Hi @FreemanZ 

I'm creating box and whisker plots currently (I will likely create other visualisations in future based on the same values) - where [Include in graph] is along the X-axis and another data field that is summarised by location is entered as the value. I use the visual filters to remove -1 values from [Include in graph].

Unformatted visualisation below...

[SamsonTruong]

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

[AngelaB]

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

[SamsonTruong]

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.

[AngelaB]

Thank you @SamsonTruong ! I will have a play and see if I can get those to work for me.

[FreemanZ]

hi @AngelaB ,

Calculated column and intermediary columns are good to start with.

Supposing location and Timepoint are the raw data, what kind of visual are you trying to create?