Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lcerrapa
Helper I
Helper I

Differences between Power BI Desktop and Browse Database in SSAS Tabular

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:

 

Image_1.png

 

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

 

Image_2.png

 

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

 

Image_3.png

 

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

 

Image_4.png

 

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

 

Thanks in advance.

1 ACCEPTED SOLUTION

Hi @lcerrapa,

 

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

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

6 REPLIES 6
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

Hi @lcerrapa,

 

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

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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
Resolver I
Resolver I

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

 

Hello.

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.