Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
danyeungw
Helper II
Helper II

How have the calculated table refresh when the direct query table re-queried

I created a calcuated table in Power BI named Combined (see code below). The "Actaul Direct" table is DirectQuery mode, but each time "Actaul Direct" was refreshed and "Combined" didn't. I think it maybe because "Combined" has Import mode. 

 

How can I have "Combined" refreshed with the new data each time "Actual Direct" is refreshed? Thanks.

______________________________

Combined = UNION(
SELECTCOLUMNS('Actuals Direct',"Month",'Actuals Direct'[Mth],"Product",'Actuals Direct'[Product],'Amount'[Amount]),

SELECTCOLUMNS(CALCULATETABLE('Forecast',"Month",'Forecast'[Month],"Product",'Forecast'[Product],'Amount'[Amount])
)

8 REPLIES 8
ToddChitt
Super User
Super User

I highly suggest you have an independent Date dimension that joins to each of the three fact tables (Actual, Plan, and Forecast). Actuals are at a daily level, correct? That should not be an issue. But the others are what? Monthly? Then you need to decide if that value is realized on the FIRST day of the month, or the LAST. FIRST is easiest as you can create a date column in that fact table base on the month, year, and literal value of "1" (in the correct order, of course). Now join that column to the Date dimension. 

That should do it. You should now be able to slice and dice by Year and Month for all three facts.




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





Thanks.

danyeungw
Helper II
Helper II

Hi @ToddChitt, It is a very good question. The user's request was to go back 90 days. For example, what was the numbers as of 8/3/2023 or any date. Not as of today or now. Do you have any suggestions? Thanks.

Sorry, I guess I'm not understanding the requirement. Users want to see what the data looked like as of a certain date? Why not just bring in (IMPORT mode) all the data and then slice or filter it on the report for the date desired?

What am I missing?




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





The key is Product+Year+Month. "Actaul Direct" can have past 90 days. For example, Producta202308 for last 14 days. That means there are 14 multiple keys in "Actaul Direct" .The key in the "Forecast" is only one Producta202308. It became one to many join instead of one to one.

 

IF the direct qeruy brings in one day, the join is one to one. 

OK, so now that I understand your data, and forgetting the whole DIRECT QUERY vs IMPORT thing, what is the problem you are trying to solve? Why would you want to combing ACTUAL with FORECAST? Those are two different FACTS with two different granularities.

Do you want to compare ACTUAL vs FORECAST? If so, put a Date table between them. 




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





Yes, actaully actuals, forecast, and plan. Sorry I left out plan. I thought if I resolve "Forecast", I can resolve "Plan". "Plan" has the same key as "Forecast".

ToddChitt
Super User
Super User

I have been uing Power BI since before it went General Availablility and build hundreds of models. So far, I have only seen ONE real case that required DIRECT QUERY. Yet I see all kinds of threads from people asking how they can make Direct Query work.

Please take a step back and ask yourself, or your business users: "What is the business requirement for having Direct Query (in other words, REAL TIME) data?" And: "Can you live with, say, hourly refresh throughout the day?"

If they absolutely insist on DQ, then do that, but also spin up a Usage Metrics report in the Workspace where the model is deployed and watch it over the next few days, weeks, months. After three months you will probably find that usage has tailed off and people are looking at it once a day. 

I know that's not an answer to your question, but I really think it needs to be mentioned.




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.