cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Different value in mdx measure

Hello everyone!!

I need some help here.. I'm using a multidimensional cube and have some doubts about how power bi "show" my measures.

My measure mdx is:

iif([Reference Date].[Year].level.ordinal = 0,null,
iif([Reference Date].[Quarter].level.ordinal = 0,
avg(
null : ancestor(
[Reference Date].[Month].currentmember,
[Reference Date].[Month].level
),
[Measures].[Number Employees]
)
,null)
)

When I use my measure in excel and filter some month the calculation works fine but when I filter in power bi the "average" returns the number of active, I believe power bi apply filters before "get" the result from cube.

We can see the difference between "full table" and filtered table using power bi (different values in august 955,6 vs 930)

We can see the difference between "full table" and filtered table using excel (same values in august 955,6 vs 955,6)

Do you have any idea?

5 REPLIES 5
Community Support Team

## Re: Different value in mdx measure

Hi @Anonymous,

I do not know your logic of average, so I make an example to explain how to meet your requirement in power bi, you can use All() and CALCULATE() function to make the calculate of average value ignore the outer filter.

```AVG =
CALCULATE (
AVERAGE ( [Number of Employee] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] <= SELECTEDVALUE ( 'Table'[Date] )
))```

BTW, pbix as attached.

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

## Re: Different value in mdx measure

I'm sorry if a I'm not so clear.. I'm using muldimensional cube with live connection, so... I can't create dax measure...

my average measure in my muldimensinal cube is:

iif([Reference Date].[Year].level.ordinal = 0,null,
iif([Reference Date].[Quarter].level.ordinal = 0,
avg(
null : ancestor(
[Reference Date].[Month].currentmember,
[Reference Date].[Month].level
),
[Measures].[Number Employees]
)
,null)
)

New Member

## Re: Different value in mdx measure

Hi,

I'm with the same problem.

Why the same SSAS measure shows differents values at Excel (pivot table) and PowerBI (SSAS live connection)?

tks

Highlighted
Community Support Team

## Re: Different value in mdx measure

Hi @Anonymous ,

I think it mainly caused by that Power BI will get only the Filtered data calculated while excel still get all data and show the filtered date.

you can try to calculate the average value in the entire data table but filtered by the date by modifying your MDX measure.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

## Re: Different value in mdx measure

Is this a bug or is the expected power bi calculation?
You know.. in my mind the "microsoft calculations" should be the same in any microsoft product... or no?

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!