I follow the instructions from https://docs.microsoft.com/en-us/power-bi/report-builder-shared-datasets
But when I get to posting the DAX query into the query designer - the designer is not available. Paste the DAX into the text field then If I refresh fields, I am prompted for credentials. This fails, then the error returned is "Value cannot be null. Parameter name: uriString"
The data source for the Power BI dataset that I'm trying to connect to is a direct query to a SQL Database.
I also raised a comment on that page and was asked to raise it as an issue.
As a work around, paginated reports could query the DB directly, but Paginated Reports does not support RLS, where as Power Bi datasets do. So another work around is to use an AS data connection from paginated reports to the Power BI dataset as that will still direct query back to the database and support RLS.
Doco says
For datasets that use a Live Connection to Analysis Services, you can connect directly by using the underlying Analysis Services connection instead of a shared dataset.
Is Direct query a known limitation and not documented or is there an issue?
2 Comments
v-yuta-msftCommunity Support
There are currently a few limitations to using DirectQuery:
-
All tables must come from a single database, unless using composite models
-
If the Query Editor query is overly complex, an error will occur. To remedy the error you must either delete the problematic step in Query Editor, or Import the data instead of using DirectQuery. For multi-dimensional sources like SAP Business Warehouse, there is no Query Editor
-
Time intelligence capabilities are not available in DirectQuery. For example, special treatment of date columns (year, quarter, month, day, so on) is not supported in DirectQuery mode.
-
Limitations are placed on DAX expressions allowed in measures to ensure that queries sent to the underlying data source have acceptable performance.
-
There is a one-million-row limit for returning data when using DirectQuery. The limit does not affect aggregations or calculations used to create the dataset returned using DirectQuery, only the rows returned. For example, you can aggregate 10 million rows with your query that runs on the data source, and accurately return the results of that aggregation to Power BI using DirectQuery as long as the data returned to Power BI is less than 1 million rows. If more than 1 million rows would be returned from DirectQuery, Power BI returns an error.
Regards,
Jimmy Tao
-
SivaManiResident Rockstar
I'm also facing the same issue. I developed a report using a DAX query in the Power BI report builder. It was perfectly working till the last update. Now, It is not working/allowing to edit the query. this is happening with the recent update. As I look at the query designer, I couldn't find any option to change MDX to DAX query designer.
Please do help me.