Difference between two measures filtered with MDX SSAS

Asked Jun 05 '17 at 10:24

Active Jun 07 '17 at 11:03

Viewed 680 times

I'm having an issue concerning a calculated member that calculates the difference between two calculated members. that's how my measures looks like in SSAS Browser :

Phase    Measure 1       Measure 2
 P1         50              30
 P2         123             49
 P3         14              69

I created a calculated member as Calculated Member 1 -Calculated Member 2 But when I using a filter with two different Phases I get a blank values. For instance From P1 : 50 - From P2 49 I get a blank value. Phase is a dimension attribute. I'm using Power BI and I user a filter over Phase for each Measure.

MDX Calculations so far :

CREATE MEMBER CURRENTCUBE.[Measures].[Measure 1]
AS ([Indicateur].[LEVEL5].&[R522],[Measures].[Montant]), 
FORMAT_STRING = "# ##0,00;-# ##0,00", 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Fact DAF1';


CREATE MEMBER CURRENTCUBE.[Measures].[Measure 2]
AS ([Indicateur].[LEVEL5].&[R500],[Measures].[Montant]), 
FORMAT_STRING = "# ##0,00;-# ##0,00", 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Fact DAF1';

Difference : [Measures].[Measure 1] - [Measures].[Measure 2]

How to solve this ?

edited Jun 05 '17 at 14:42

asked Jun 05 '17 at 10:24

El houcine bougarfaoui

35,805
9
37
37

Can you post all the MDX calculations involved? – GregGalloway Jun 05 '17 at 12:10
Post Edited.... – El houcine bougarfaoui Jun 05 '17 at 12:44
Are you sure that's the exact MDX? Measure 1 and 2 are identical – GregGalloway Jun 05 '17 at 14:41
Sorry, corrected – El houcine bougarfaoui Jun 05 '17 at 14:42
what dimension does Phase come from? Do you filter to two phases in the calculation or in Power BI? – GregGalloway Jun 05 '17 at 14:45
It comes from a dimension called [Indicateur]. I use two filters in Power BI, each filter concerns a measure. – El houcine bougarfaoui Jun 05 '17 at 14:47
what filter do you put on each measure? And can you screenshot the Attribute Relationships tab from the Indicateur dimension and post it. That may be related. – GregGalloway Jun 05 '17 at 17:03
I put Phase filter. I don't think the problem is related to Indicateur dimension. – El houcine bougarfaoui Jun 06 '17 at 09:26
I'm not saying the attribute relationships are wrong but it might explain why you are getting null – GregGalloway Jun 06 '17 at 09:47
Is [Measures].[Montant] native measure or calculation? It may appear an issue as well. – Danylo Korostil Jun 07 '17 at 10:45
it's a native measure – El houcine bougarfaoui Jun 07 '17 at 11:00
what's [Indicateur].[LEVEL5].&[R522]? – Anton Jun 13 '17 at 05:08
have you run SQL Profiler against your SSAS cube to see what command PowerBi sends during that action? – Anton Jun 13 '17 at 05:14
perhaps phases p1,p2,p3 are categorized as disjoint attributes of [Indicateur].[level 5] hierarchy and because youre trying to difference of different tuples ::: i.e **From P1 : 50 - From P2 49** – PrinceKayastha Jun 14 '17 at 09:12
So how to solve this ? – El houcine bougarfaoui Jun 14 '17 at 09:15
@Anton no i didn't. [Indicateur].[LEVEL5].&[R522] is an Hierarchy attribute. – El houcine bougarfaoui Jun 14 '17 at 09:16

Difference between two measures filtered with MDX SSAS

0 Answers0