Forum Discussion
Static Data alongside Live Data
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
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 💡
5 Replies
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 💡Perfect, thank you so much.
I now get this error message upon publishing - I am unable to view these workings Online?
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.
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?
