Different value in mdx measure

[Anonymous]

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?

[v-lid-msft]

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

[Anonymous]

Thanks for your answers.

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)
)

[v-lid-msft]

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.

[Anonymous]

Thank you for reply.

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?

[vandereduardo]

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