DirectQuery with RELATED, LOOKUPVALUE, or equivalent

[bigsky5829]

Hi,

I have two data sources. One is an API call to a table in our smartsheet.com account and the other is a SQL Server. I need the data to be live, i.e. if a user updates something in the SQL Server or smartsheet (example: number of expected hires in the forecast on a future date), the PowerBI report should immediately reflect the changes.

I need to create a calculated column based off of two columns in the different data sources. It needs to be a column since it will be used in slicers for filtering the report. I've tried merging the tables in PowerQuery but it doesn't support that in DirectQuery mode. I've also tried building relationships between the two tables with dim_date and dim_district dimension tables or a synthetic key directly between the tables. That works great for building the visualization table and I can show all the data in one spot, but I get errors trying make my filter:

Exception_Filter = 
IF(
    source_1[Approved Class Size] <> source_2[Forecast Hires Targeted],
    TRUE,
    FALSE
)

I've tried variations of this with RELATED and also simply adding columns to source_1 from source_2 with LOOKUPVALUE but nothing seems to work. I've also tried building a dax table but it doesn't update live like the table with only the SQL data updates.

[v-yohua-msft · Accepted solution]

Hi, @bigsky5829 

It's important to understand the limitations of DirectQuery. When using DirectQuery, some operations, such as merging tables or creating calculated columns with data from different sources, may be limited or behave differently than expected. This is because DirectQuery doesn't import data; Instead, it queries the underlying data source directly. For more insights about DirectQuery, you can refer to the DirectQuery documentation in Power BI:

DirectQuery in Power BI - Power BI | Microsoft Learn

If you're not already using a composite model, consider using a composite model. This feature allows you to combine DirectQuery and imported data into a single report. While this doesn't directly solve the problem of real-time updates of imported data, it may provide a workaround that allows you to import data from one source while using DirectQuery for another. More details on composite models can be found here:

Use composite models in Power BI Desktop - Power BI | Microsoft Learn

For SQL Server data, if you have the option to migrate to Azure Synapse Analytics, you can provide improved performance and functionality through DirectQuery. Azure Synapse Analytics supports real-time analytics and potentially provides a more robust backend for Power BI reports. For information about using Azure Synapse Analytics with DirectQuery in Power BI, you can check the following link:

Azure Synapse Analytics (formerly SQL Data Warehouse) with DirectQuery - Power BI | Microsoft Learn

For Smartsheet data sources, consider exploring the option to trigger a Power BI refresh when data is updated in Smartsheet. This may involve using Power Automate to monitor changes in Smartsheet and trigger a dataset refresh in Power BI. While this method doesn't provide instant updates, it can reduce the latency of the data refresh cycle.

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.