Forum Discussion
Help with complex DAX measure
Hi idrabble
From my understanding that would be due to the fact that when the measure is being calculated in the Matrix it is doing it in the row context of the matrix.
So when the total is calculated there is no row context, so the measure is then defaulting to the zero condition of the measure.
If you want this to be calculated correctly, my best advice is you will need to do the following example from the Power Pivot Pro, which explains in detail how to get this working as expected.
I am still unable to get this to work.
I have tried everything suggested so far with no success, so I thought I would go back to basics
By using the following I can get a Matrix to function almost correctly:
2016 <1 = if(Measures_Table[Revenue (-1Yr)]<1,1,0) -- where [Revenue (-1Yr)] = CALCULATE(sum('Sales Data Last 5 years'[Revenue AUD]),FILTER('Calendar','Calendar'[FiscalYear]=[Current Fiscal Yr -1]))
2017 >499 = if([Revenue This Yr]>499,1,0) -- where [Revenue This Yr] = CALCULATE(sum('Sales Data Last 5 years'[Revenue AUD]),FILTER('Calendar','Calendar'[FiscalYear]=[Current Fiscal Yr]))
Then Conquest Customer = if(and(Measures_Table[2016 <1]=1,Measures_Table[2017 >499]=1),1,0)
If I then drop this into a Matrix of all Customers I can identify which are conquests as they are defined correctly as 1's
However, even though these values are formatted as whole numbers, they do tally correctly. I have seven 1's but get a total at the bottom of the Matrix of 0
Now obviously when I use the measure in a Card, I still get 0
Any further help would be appreciated
Ian
Super UserHi idrabble
From my understanding that would be due to the fact that when the measure is being calculated in the Matrix it is doing it in the row context of the matrix.
So when the total is calculated there is no row context, so the measure is then defaulting to the zero condition of the measure.
If you want this to be calculated correctly, my best advice is you will need to do the following example from the Power Pivot Pro, which explains in detail how to get this working as expected.