Aggregation Option Issue in Power BI Desktop with Azure Analysis Services

[Anonymous]

Hey all,

I'm currently doing a live connection from Azure Analysis Services to Power BI Desktop. The connection is successful and I see my dimensions and fact table showing up in the report.

The issue is that whenever I try to create some visuals, I cannot make a column aggregate (i.e. sum, count). This has happened with my current model where I'm not able to aggregate data in the reporting layer. Screenshot below of the options I should be normally seeing vs. what I currently see with my current model connection:

Typical Aggregation Option View - Using Data Source from a DirectQuery (non-Analysis Services)

Current Option View with no Aggregation Options shown (Using Azure Analysis Services)

Any feedback or suggestions are appreciated, especially if there is something that has to be done at the Analysis Services layer. Thank you!

[marcstoffels]

I have the same issue, no aggregation options in Power BI Desktop for columns, when connecting to our tabular cube in Azure, using model 1500.
We migrated our tabular model from 1400 to 1500.
The 1400 model still shows the aggregation options in Power BI Desktop (October 2020 version).
Is this still an open issue, or is it with the 1500 version obligated to create a measure (on each column) in your model for aggregation purposes?

[marcstoffels]

I have found the answer here:
https://github.com/otykier/TabularEditor/issues/636

marcosqlbi commented Oct 3, 2020
This is by design: calculation groups automatically disable implicit measures.
However, using explicit measures is a best practice when you create a model you share with other users.
Documentation: https://docs.microsoft.com/en-us/analysis-services/tabular-models/calculation-groups?view=asallprodu...

This documentation:
Calculation groups work with explicit DAX measures. In this example, Sales is an explicit measure already created in the model. Calculation groups do not work with implicit DAX measures. For example, in Power BI implicit measures are created when a user drags columns onto visuals to view aggregated values, without creating an explicit measure. At this time, Power BI generates DAX for implicit measures written as inline DAX calculations - meaning implicit measures cannot work with calculation groups. A new model property visible in the Tabular Object Model (TOM) has been introduced, DiscourageImplicitMeasures. Currently, in order to create calculation groups this property must be set to true. When set to true, Power BI Desktop in Live Connect mode disables creation of implicit measures.

You can change this property to "false", but then the calculation groups functionality will not work:
"discourageImplicitMeasures": true,

So, this means indeed that we should create measures for each column to be aggregated.

[Anonymous]

Hey all,

Thanks for providing insights on how to best approach this topic. It seems like it's an issue that warrants a support ticket. The server is run on Azure Analysis Services, so it's set by default to be in tabular view. Screenshot below with sensitive information redacted.

I will work on using the Support Ticket system to get this resolved. Thank you everyone!

[Anonymous]

I am experiencing the same issue. Were you able to find a solution after submitting a support ticket?

[Icey · Accepted solution]

Hi @Anonymous ,

Do you connect to Tabular or Multidimensional? 

I tested with SSAS(on-prem).

For Tabular, columns could be aggregated.

But for Multidimensional, columns couldn't be aggregated. I'm not sure if it was possible before. 

In your scenario, if last week, (1) it was connected to Multidimensional and (2) columns could be aggregated. But it doesn't work currently and you are a pro user, it is suggested to create a support ticket to get further help.

Best Regards,

Icey

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

[amitchandak]

@Anonymous , what is the data type of Name. Is it a Measure?

[Anonymous]

Hey @amitchandak ,

Thanks for replying. The Name column currently shows a data type of "Text" and is a table column, not a measure. The aggregates showed up last week, but now they do not appear at the moment.

This issue also applies to other fields with a data type of "Decimal Number".

[amitchandak]

@Anonymous , Check if you are facing after May 2020 release upgrade. If so check and log ticket at : https://community.powerbi.com/t5/Issues/idb-p/Issues