Strange scenario this one.
I have a PBIX covering management accounting info which works fantastically, Star Schema, fast measures, nice drill down and thru, more slicers than you can poke a stick at. All functioning well.
We have a requirement to produce some pages of this for third party use. So for Analyse in excel or Pag reports, I’ve written the necessary data feed - a long DAX script (actually a union of 3 long DAX scripts) which generate the necessary data to feed to Excel or Pag Reps. (22 measures in here!)
This DAX takes a long time to run, so approach was to generate the export data as a physical table on model refresh. This works in the service under premium per user workspace, but a refresh in pro workspace or desktop suffers from memory problems. Again this has been overcome by commenting out the table build in desktop and uncommenting each of the three DAX scripts after refresh. This works and enables publishing to service but not ideal.
In an attempt to overcome this I thought I’d have a separate PBIX linking to the original one getting data from the service. But in order to add the new table I am forced into direct query mode, the data refreshes intermittently in desktop but in service we get following error:
“Single sign-on (SSO) credential is not provided which is required to execute the current request. Note that performing a refresh over a dataset with a calculated table or a calculated column which references a Direct Query data source is not supported within the Power BI service.”
Apologies for long background but I’m a bit lost as to how to make this easier for then end user/publisher. Questions:
-
Will microsoft make the above error go away? When?
-
Is there a way of building additional tables in a new dataset from a dataset in the service without going into Direct Query Mode?
-
Any other ideas greatly appreciated
Looking fwd to help
Pete