Differences between Power BI Desktop and Browse Database in SSAS Tabular

[lcerrapa]

Hello.

I don’t understand this thing in Power BI Desktop. I have two measures with this definition:

Difference between total of two columns:
z_Columns_Difference := CALCULATE( SUM([Final Existences]) - SUM([Initial Existences]) )

Total of the previous measure regardless the month:
z_Total_All_Months:=
  CALCULATE(
      [z_Columns_Difference];
      ALL(Calendario[Mes])
  )

If I analyze in Excel from Visual Studio 2015, the result is correct:

If I browse the Analysis Services database from SSMS, the result is the same:

But if I create a new report in Power BI Desktop, connecting with the same analysis services, the result is the following (Connecting live):

There is the same result for both columns. What is happening? I have the last versión of Power BI Desktop:

And I have a compatibility leve 1200 in Analysis Services Tabular.

Thanks in advance.

[v-shex-msft]

Hi @lcerrapa,

It seems like the all filter has been ignored,
Please check your relationship if it has been set to single. if this is a case, you can modify it to both.
In addition, you can also try to filter by table instead the column:

z_Total_All_Months:=
  CALCULATE(
      [z_Columns_Difference];
      ALL(Calendario)
  )

Regards,

Xiaoxin Sheng

[lcerrapa]

Hello.

This solution doesn't fix my problem, because there is another filter in the year (in the same calendar table). If I use 'All(Calendario)' it ignores this filter year.

Thanks for your answer.

[v-shex-msft · Accepted solution]

Hi @lcerrapa,

You can try to use allselected to instead, it doesn't ignore the original filter.

Regards,

Xiaoxin Sheng

[lcerrapa]

Hi @v-shex-msft

If I change the definition this way as you say:

z_Total_All_Months:=
CALCULATE(
  [z_Columns_Difference];
  ALLSELECTED(Calendario)
)

The result is right. Many thanks for your time.

[mmanwaring]

Hi

I notice that i can recreate this result which does not apply the ALL to the month name if you have the month name sorted by another column.

For me, this is a bug as you would expect the ALL to clear the filter context for that month column regardless or sorting?

Regards,

Mike

[lcerrapa]

Hello.

Thanks for your answer. In fact, the column of the month name is sorted by another column (monthcode).