Direct Query Workarounds

[shuhn1229]

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!

[shuhn1229]

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

[lbendlin]

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]

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]

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]

Hi,

Thank you!

Do you mind pointing out how to import another powerbi dataset using analysis services? I can open another thread If you'd prefer. Specifically, what to supply for server if the report is published to PBI cloud.

Steve

[lbendlin · Accepted solution]

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.