Forum Discussion

shuhn1229's avatar
shuhn1229
Resolver I
2 years ago
Solved

Direct Query Workarounds

Hi all,

 

I have a model imported into BI desktop that uses direct query & there is unfortunately no way around this. In the direct query data source there is a "program" column. I am trying to make this program list more granular by pulling items from another table. The new calculated column would then by used as a legend for a visual. To do this I was aiming for a simple If function e.g. If ('Column' = "X", Lookupvalue (...column y), however based on direct query this is not allowed. Any thoughts here? As a side note, I cannot create calulated tables either as this would mess up my refresh schedule.

 

thanks!

 

Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    First grab the server settings from your dataset:

    Then in Power BI Desktop select "Get Data...Analysis Services"

    Make sure to select Import, and specify the address from above

    If you know the table you want to grab you can also supply the DAX query

     

     

    After that, do your Power Query transforms and load the data source.

     

     

     

     

6 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    You can only create calculated columns in a Direct Query data source with local model if the column pulls data from the same row of that data source  (and not from other tables, as you have found out).

     

    What is your Direct Query data source?  A Power BI dataset?  You know you can access these in import mode via the Analysis Services connector, right?

    • shuhn1229's avatar
      shuhn1229
      Resolver I
      2 years ago

      Hi,

       

      The connection is in analysis services and when I try to import I get the following error:  Resource Governing: This query uses more memory than the configured limit. The query — or calculations referenced by it — might be too memory-intensive to run. Either reach out to your Analysis Services server administrator to increase the per-query memory limit or optimize the query so it consumes less memory.

       

      So I guess Import is out. But unrelated, If I did want to not use direct query from a PBI dataset what would I supply for server and database? That's a neat trick.

       

      Thanks

      Steve

      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago

        Don't try to ingest all cube dimensions.  Do it one by one, only for the tables required for your calculated column.  Keep accessing the rest of the cube as is.

  • shuhn1229's avatar
    shuhn1229
    Resolver I
    2 years ago

    Thank you. Do you mind pointing out how I would do direct import of a PBI dataset? I can open another thread if you'd like

     

    Steve