Forum Discussion

db042190's avatar
db042190
Impactful Individual
3 years ago
Solved

direct query vs dax layer

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?

Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    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

     

5 Replies

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Community Support
    3 years ago

    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's avatar
      db042190
      Impactful Individual
      3 years ago

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

      • v-yueyunzh-msft's avatar
        v-yueyunzh-msft
        Community Support
        3 years ago

        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