direct query vs dax layer

[db042190]

hi we think the refresh max of 48 times per day wont be frequent enough to satisfy one of our pbi requirements.  so we start thinking about direct query but notice that perhaps adding a very important dax summary over a direct query isnt possible.  thats because we dont see the data icon (just viz and model) when direct query is chosen.  is there another way to add a dax layer over a direct query?  or will we be forced to make the direct query do the summarization itself in t-sql thus taking us off our strategy of building very generic datasets for each area of the business and then using dax etc on an as needed basis report by report?

[v-yueyunzh-msft]

Hi , @db042190 

According to your description, the refresh max of 48 times per day wont be frequent enough to satisfy one of our pbi requirements.

Then you do need to consider using the Direct Query connection mode.

For Direct Query mode, you can create both measures and calculated columns,However, there may be limitations for some DAX functions.

This is because under Direct Query mode, all computed columns and measures need to support query folding, and you need to be able to translate DAX into the language of the underlying data source, so that each query sends a request directly to the underlying data source.

And Direct Query doesn't have a data layer, so you don't have a way to see the data directly in Desktop, you can drag your data to Visua to get it.

For more information, you can refer to :
DirectQuery in Power BI - Power BI | Microsoft Learn

Query folding guidance in Power BI Desktop - Power BI | Microsoft Learn

Another option is to use a SQL Server Analysis Services (SSAS) tabular model as an intermediary between the DirectQuery data source and Power BI. You can use DAX to create calculated measures and columns in the SSAS model, and then connect to the model from Power BI using DirectQuery.

Fore more information, you can refer to :
Connect to Analysis Services tabular data in Power BI Desktop - Power BI | Microsoft Learn

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[db042190]

thank you but without the data icon, what clicks/steps do we follow to add the allowable calcs and measures?

[v-yueyunzh-msft]

Hi , @db042190 

You can try to click One table in the "Data" Pane, and you can click "New Measure" or "New Column" to create a measure or calculated column.

Best Regards,

Aniya Zhang

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

[db042190]

do you mean in "modeling" mode?  i do not believe there is a data pane available when direct query is chosen.

[v-yueyunzh-msft · Accepted solution]

Hi, @db042190 

Sure, there is no data pane in Direct Query mode , but you can also create some measures or calculated column in Report view or in the Model View.

Best Regards,

Aniya Zhang

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