Static Data alongside Live Data

[Michael_S_1984]

Hello

I have an imported dataset/table from another pbix that I have chosen to no longer refresh - the hope is that it allows me to have a 'Baseline' dataset at a point in time, to then compare with a 'Live' version (duplication) of the same report that does indeed refresh.

The data is based upon a SharePoint list, and I have therefore created a one:to:one relationship via the GUID column.

As you would expect, any new rows that are subsequently uploaded in the 'Live' version will not pull through to the Table visual whereby I have those side-by-side.

A simple example:

Item	Baseline_Value	Live_Value
1	£10	£10
2	£12.50	£10
3	£15	£20
4 (new)		£15
5 (new)		£10

Is anyone able to suggest a work-around that would allow Scheme 4 and 5 to pull through, so that I can continue to view the variance for all schemes?

(I have tried the other variations of relationships without any success).

Thanks

Michael

[DataVitalizer · Accepted solution]

Hi @Michael_S_1984 

Your baseline table is frozen, so it will never create new rows, in order to see new items from Live (like 4 and 5), let the Live table drive the rows and just pull in Baseline values where they exist:

• In Power Query, merge Live with Baseline on the GUID using a Left Outer join and expand the Baseline column.

• Or in DAX, add a column in Live with:

Baseline_Value =
LOOKUPVALUE(Baseline[Baseline_Value], Baseline[GUID], Live[GUID])

That way all Live rows show up, with blanks for Baseline when there’s no match.

Did it work? 👍 A kudos would be appreciated
🟨 Mark it as a solution to help spread knowledge 💡

🟩 Follow me on LinkedIn

[Michael_S_1984]

I now get this error message upon publishing - I am unable to view these workings Online?

[Michael_S_1984]

Perfect, thank you so much.

[MFelix]

Hi @Michael_S_1984 ,

When you refer that you want to pull trough 4 and 5 do you want to have that value on the baseline? And if yes what is the expected value is it the same has in the line value?

If you have a baseline value (the one that you no longer refresh) and that does not include 4 and 5 you won't be able to get those values to your baseline, only option I can see and this depends on how you have your model setup is to make a append of the historical and the new lines that are not available in the baseline.

Be aware that depending on the datasources since you are making a combination of tables you can have refresh problems, so without any further information about your model is not easy to give you the best approach.

[Michael_S_1984]

Thanks for your reply - I would like/need to have line  4 and 5 pull through with blanks for the Baseline, as they were not originally included in that particular (static) dataset.

How would I make an append?