Support multi-entity filters on Multidimensional models for SQL Server 2017

[muchinski]

When PowerBI is connected to SSAS MD 2017 RC2, it submits the DAX query for cross filtering using TREATAS statement when there are more than one filter applied (ie: Pie Chart with Legend, Stacked Column Chart, New Matrix etc.).
If the source is Multidimensional, the query fails.

(On SQL 2017, before RC2, it was reporting Internal error: An unexpected exception occurred. - https://connect.microsoft.com/SQLServer/feedback/details/3134438/any-dax-query-with-treatas-filter-f...)

P.S: Same model deployed on SSAS 2016 works fine (using FILTER not TREATAS). A similar model on Tabular, also works fine.

[v-haibl-msft]

@muchinski

Are you using the latest Aug 2017 version of Power BI Desktop? Could you please help collect the fiddler traces during repro and share the .saz file through online file service like OneDrive?

Best Regards,
Herbert

[muchinski]

Hello @v-haibl-msft,

yes, I am using the last version of Power BI Desktop. 

As I understood, Fiddler can be used to troubleshooting visualizations issues on the browsers, but the problem that I reported is happening when we connect in Multidimensional Model even with Power BI Desktop (not via browser). In this case, I don’t know how I could use Fiddler to trace.

I did the trace using Profiler on my Multidimensional model, and realized that with the last version, the Power BI is not even sending the query in this case. It seems that it knows that the TREATAS command is not supported by AS 2017 RC2.

I have 2 environments with the same model running: One with SQL Server 2016 and other with SQL Server 2017 RC2 for testing purposes.

For SQL Sever 2016, Power BI send the query bellow.

EVALUATE
  ROW(
    "Difference_Order", 'Order Intake'[Difference Order],
    "Order_IntakeDifference_Order", CALCULATE(
      'Order Intake'[Difference Order],
      KEEPFILTERS(
        FILTER(KEEPFILTERS(ALL('Date'[Year-Month])), 'Date'[Year-Month] = "2016-10")
      ),
      KEEPFILTERS(
        FILTER(
          KEEPFILTERS(ALL('Sales Line'[Sales Line.Key0])),
          'Sales Line'[Sales Line.Key0] = 1
        )
      )
    )
  ) 

BUT, when I connect to the same model on SQL 2017 RC2, power BI just show the error message and do not even sent the query to SSAS. (I mean, the query to get the highlighted value on the right graph)

On the previous RC and with July version of Power BI (when I opened the bug on connect), it would send the query with TREATAS statement; witch now is not supported in SSAS Multidimensional.

Could you please clarify if Power BI multi-entity filters will be supported when connected to SQL 2017 SSAS MD, because, if not, Power BI will look buggy in many models.

[v-haibl-msft]

@muchinski

I've sent mail to related team to confirm about it.

Best Regards,
Herbert

[v-haibl-msft]

@muchinski

I’ve got response from the Product Team.

SSAS MD 2017 RC2 has TreatAs disabled which is required for multi-entity filters. The next update for SSAS should allow this combination of features. I'll notify SSAS of this. The error message is improved in the upcoming Power BI Desktop release. Work is understood and planned for supporting multi-entity filters against models without TreatAs, but there is no timeline for it in lieu of other improvements. The user can vote for multi-entity filters against older models on ideas.powerbi.com.

Best Regards,
Herbert

[muchinski]

Hello @v-haibl-msft,

Thanks a lot for your explanation.
As I mentioned, the filters work correctly when the multidimensional model is deployed on SQL Server 2016. I can suppose that Power BI choose the DAX syntax based on Server Version, which in both MD and Tabular for SQL 2017 RC2 are 14.0.1.430.
On the other hand, we have the Supported Compatibility Level. In this case AS MD 2016 and MD 2017RC2 are 1100 and Tabular 2017RC2 is 1200.
Hypothetically speaking, should it be the Supported Compatibility Level instead the Server Version the variable used to choose the correct syntax?
(anyway, probably for the next version, I will migrate the models to Tabular to stop walking against the wind)

[Anonymous]

Hello,

I recently upgraded to SQL2017.... to discover that most of my reports are not working fine anymore because of that issue 😕

Was there any progress since it was reported 8 months ago ?!

The fix should be pretty simple, use 2016 compatible feature until SSAS2017 can actually handle that request ???

[v_kosimovsky]

I have the same problem. What is the solution for it?

SSAS Multidimensional 14.0.213.1, Power BI Version: 2.56.5023.1043 64-bit (March 2018)

[tomhafner]

Is there still no solution for that? Have SQL 2017 14.0.223.1 (CU8) u. PowerBI 2.59 (June 2018) in use.

Since the error is already known for a year (SQL2017 RC2) I would expect a solution already!

How do you explain to your customers that after the update to 2017, reports will not work anymore and he'll have to rebuild them somehow just because Microsoft. The customers who use PowerBI are very well paying enterprise customers - which one do you really want to annoy?