Getting live data via a calculated table

[markmsc]

Hello team,

I have a data mart application with an architecture like this:

Step 1: Data is replicated into the mart database via native SQL replication.  It lands in a copy of the source table.  Call this table T1.

Step 2: An ETL process runs to take the changed data in T1 and process it into a mart dimension table.  Call this table T2.

Step 3: Power BI model incremental refresh runs every 30 minutes to pull the latest data from T2 into an import model copy of the table for reporting.  Call this import model copy table T3.

Because each of these steps happens on its own frequency, I occaionally get the question, "I've updated my data in (source system), why isn't it in the mart reports?"  Accordingly, I would like to develop an audit view for the end users so they can see the progression of their data changes from T1 to T2 to T3.  Basically a live look at where their data is in the process of the three steps.

My first solution for this goes like so:

1. I already have the import model for reporting that contains T3.  To this, I add DQ copies of T1 and T2.

2. Next, create a caclulated table in the model that unions a common status query run against the DQ copies of T1 and T2, and the import copy of T3.

3.  Last, create a PBI visual that pivots the calculated table so that I can see the source table name and the status at T1, T2, and T3 all in a row in the same matrix.

My thinking here was that when I run the report containing the visual, I'd get a live look at the status of T1 and T2 (since the calculated table is sourcing them via DQ), combined with the state of T3 as of the last model refresh.  

Alas, not the case.  I've not worked with a calculated table in this way before, and so I learned here that the live portion of it doesn't work the way I was hoping.  The table itself has to be recalculated to get the latest (just querying it via the report won't do it), and so my viz will only display the status of T1 and T2 (along with T3) as of the last model refresh.

Any suggestions for a different way to accomplish what I want?  I think my ability to do things inside a pbix is limited since I am dealing with a published semantic model, and again I need to normalize and bring together data from three separate tables for display in one visual, perserving the "real time" DQ nature of two of them.  

Thank you.

[Shahid12523 · Accepted solution]

• Calculated tables won’t work (they only update at model refresh).
• To get live T1/T2 with import T3 in one visual:

1. Keep T1/T2 as DirectQuery tables.
2. Keep T3 as Import.
3. Use a bridge/keys table and build measures (e.g., Status_T1, Status_T2, Status_T3).
4. Show them side-by-side in a matrix → T1/T2 always live, T3 shows last refresh.

This avoids static calculated tables and gives you the real-time audit view.

[Shahid12523 · Accepted solution]

• Calculated tables won’t work (they only update at model refresh).
• To get live T1/T2 with import T3 in one visual:

1. Keep T1/T2 as DirectQuery tables.
2. Keep T3 as Import.
3. Use a bridge/keys table and build measures (e.g., Status_T1, Status_T2, Status_T3).
4. Show them side-by-side in a matrix → T1/T2 always live, T3 shows last refresh.

This avoids static calculated tables and gives you the real-time audit view.

[markmsc]

Thank you for your reply.  I think I've concluded that what I want to do is, surprisingly, not possible.

I started going down the road of a keys table as you and most of the other respondents have suggested.  The keys table itself is quite easy, as it is just a view on the appropriate columns in T1.  But then all roads are blocked.

Because there is time delay in the data moving from T1 to T2 to T3, I can't set up a 1:1 relationship between the keys table and T2 and T3, since some key values may not exist in those two tables yet at any point.  So it has to be 1:many, which in turn prohibits the use of RELATED on the keys table to build calculated columns or measures using values from T2 and T3.  

LOOKUPVALUE would be an acceptable workaround to that problem, except that the keys table has to be DirectQuery so that the values pulled from T2 (which is also DQ, whereas T3 is import) are live.  And LOOKUPVALUE is not available with DQ tables.  Switching the keys table to Import would be OK, because the key values for each day are actually set early in the day and don't change -- except that then I won't get the live pull from T2 except when the model is refreshed.  

So, then, I think this is impossible, or least impossible to do in one PBI visualization.  

[markmsc · Accepted solution]

I have found a solution that works.  Here is a summary of my approach.

I kept my original problem description generic for simplicity, but I actually have an additional complexity that factored into the problem.  I described tables T1, T2, and T3, but I actually want to track the status of two sets of tables that are sliceable by a common dimension.  So in reality I have T1-1, T1-2, T1-3, and T2-1, T2-2, and T2-3.  And I want to lay them out in a row in a PBI visual, sliced horizontally by the dimension.

The crux of the solution was in fact a key table.  What I did:

1. The key values for tracking T*2 and T*3 all come from T*1 initially.  Rather than making a calculated table to drive off of, as I started with, I instead created a database view that brought in the T1 and T2 key values into a common structure.  The key definitions are not exactly the same between the two, but they overlap enirely except for one column, so the common structure was not complicated at all.  I use this view in DQ mode in my model.  This key value table also contains the statuses from T*-1, as long I was pulling the keys from those tables already.
2. The view also creates composite key columns for T*-2 and T*-3 to link to in the. model.  There is one composite key for T1-* and one for T2-*.
3. In the model, T1-2 and T2-2 are brought in via DQ as well.  On each of those tables in the model, I created the appropriate composite key columns and then established relationships back to my keyt value table using the composite keys.
4. Also in the model, T1-3 and T2-3 are brought in via Import.  I likewise created composite keys on those tables, and relationships back to the DQ key table via those composites.
5. Because my key value table contains key rows for both T1 and T2, the composite key columns for T1 and T2, which each only apply to their one table, will contain garbage for rows in the key table that are for the other table.  Accordingly, I had to create a synthetic key value, essentially -1, for those rows.  So if we are on a key value row for T1, the composite key column on that row for T2 is defined as IF({row = t1}, -1, {composite key for T2}), and vice versa for T2 rows and the T1 composite key.
6. I then had to add a row to each of the T*-2 and T*-3 tables matching that synthetic key, so all values in the key table would map to a row in the DQ and Import tables and there would be no RI violations.
7. With those relationships in place, lastly, I could create measures on the key value table that reach across those relationships to get the needed values from the T*-2 and T*-3 tables.  Since the key value table is DQ, I get live results from T*-1 (which are in the database view. originally), and T*-2, since those tables are also DQ; and import results from T*-3, as those tables are imported.  Exactly what I want!

One hitch in all this was the relationships.  There are no RI violations in any of the resulting relationships, and the model allowed me to pul the measures across for the T*-2 relationships just fine. But it refused to let me do so for T*-3 for some reason, giving the "no relationship in the current context" error.  Fortunatley, in my PBI report I could create the T*-3 measures anyway, so I got there in the end.  But I'll have to figure out at some point why the model doesn't like my T*-3 relationship for that use.

But, in the end, it all works.

[v-hjannapu]

Hi @markmsc ,
Thank you  for reaching out to the Microsoft fabric community forum.
I would also take a moment to thank @Shahid12523 , @pankajnamekar25 , @MohamedFowzan1  for actively participating in the community forum and for the solutions you have been sharing in the community forum. Your contributions make a real difference.

 In Power BI, calculated tables only refresh when the dataset is refreshed, so they cannot show the live status from DirectQuery tables. Because of this, it is not possible to see T1/T2 (live) and T3 (import) together in one visual exactly the way you are trying.

The practical options here are:
             Show T1/T2 in one visual and T3 in another, so users can still compare the status side by side.
 Or, create an audit/status table in your data mart/ETL itself which tracks movement from T1→ T2 → T3, and then bring that table into Power BI.

Hope this helps if you have any queries we are  happy to assist you further.
Best Regards,
Community Support Team.

[markmsc]

Hello, and thank you for your reply, and the suggestion.  Please see my replies to  @Shahid12523 for an explanation of my solution to the problem.

[pankajnamekar25]

Hello @markmsc 

You can explore any of below options

Use DAX measures  query T1 (DQ), T2 (DQ), and T3 (Import) directly, and display them side by side in a matrix.

Composite model keep T3 as Import, T1/T2 as DQ, join them via a common key, and use visuals instead of a union.

Audit fact table upstream create a dedicated status-tracking table in the mart/ETL and bring that into Power BI.

SQL view  union T1 and T2 in the database and display alongside T3 in Power BI.

[markmsc]

Hello, and thank you for your reply, and the suggestions  They were helpful in thinking through the problem.  Please see my replies to  @Shahid12523 for an explanation of my solution to the problem.

[MohamedFowzan1]

Hi @markmsc 

Calculated tables are static and only refresh during dataset refresh, so they definitely cannot reflect live changes from DirectQuery sources. Its also a limitation to actually create this with tables of different connectivity.

If possible to create DAX Measures which query the tables and provide the intended result would make it easy to displaye the fields within the same visual

[markmsc]

Hello, and thank you for your reply, and the suggestion.  Please see my replies to  @Shahid12523 for an explanation of my solution to the problem.