cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mscampos Regular Visitor
Regular Visitor

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
v-lid-msft Super Contributor
Super Contributor

Re: Different value in mdx measure

Hi @mscampos,

 

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

mscampos Regular Visitor
Regular Visitor

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

v-lid-msft Super Contributor
Super Contributor

Re: Different value in mdx measure

Hi @mscampos ,

 

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.

mscampos Regular Visitor
Regular Visitor

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
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 386 members 3,258 guests
Please welcome our newest community members: