Problem when using MDX to query Power BI Dataset

[JB81]

Hi

When using a client that sends MDX to the Power BI service to get data from a dataset we see for certain queries a FORMAT_STRING clause that kills the query with a 400 Bad Service error.  

...

On Rows
From [Model]
Where ([DIM Ship Date].[Ship Date (Month)].[Ship Year].&[2022]) Cell Properties VALUE, FORMAT_STRING

Output from an affected MDX Query with FORMAT_STRING using SSMS connected to Power BI Workspace.

Executing the query ...
The remote server returned an error: (400) Bad Request.
The remote server returned an error: (400) Bad Request.

Run complete

There is a related GitHub post for a similar problem in Excel Pivot Tables and Tabular Editor when connecting to Power BI.

Tabular Editor Measure Formatting String possible issue · Issue #643 · TabularEditor/TabularEditor ·...

I haven't been able to get a Query Plan or Server Timings in DAX Studio for the breaking queries.

Does anyone know if this bug is being investigated or if there is a workaround?

Thanks

JB

[v-zhangti]

Hi, @JB81 

Please refer to the following links in the hopes of helping you.

Solved: DataSource.Error: AnalysisServices: The remote ser... - Microsoft Power BI Community

Solved: SSMS (400) Bad Request error attempting connect to... - Microsoft Power BI Community

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JB81]

Hi

Thanks for your response and ideas.

I have checked those links but they seem to refer to all queries failing which is not our use-case.

This only affects queries generated including the clause FORMAT_STRING included, for some queries and not all.  So to be clear we can execute some queries against our dataset (that is affected by this problem).

I am getting our Power BI Admin to double check settings, just in case though.

Thanks

Jonathan

[JB81]

Hi

It looks like it's that same problem that has been reported on SQL Server 2019 CU4+ (from Jan 2020).

Does anyone know if SQL Server 2019 was patched for this bug?

Hopefully if SQL has been patched we can ask for Power BI tabular to have the same patch applied.

Thanks

Jonathan