cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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)

img1.png

 

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

img2.png

 


Do you have any idea?

5 REPLIES 5
Community Support Team
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] ) 
))

 

 

Different-value-in-mdx-measure.pngDifferent-value-in-mdx-measure-2.png

 

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

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

vandereduardo New Member
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
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

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?

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors