Forum Discussion
Referring A Measure inside Another Measure
HI Ynew
Please try this MEASURE
TotalVolume = VAR mymonth = [LastMonth] RETURN COUNTAX ( FILTER ( 'Report1', 'Report1'[Month] = mymonth ), 'Report1'[Volume] )
Actually whats going on with your formula is that...... it is returning the Full Table.
Your MEASURE is calculated for each row of the Table and returns the Last Month for each filtered row.
Thats why you need to take the MEASURE out of the CALCULATE and put it in a VARIABLE before CALCULATION
TotalVolume =
VAR mymonth = [LastMonth]
RETURN
COUNTAX ( FILTER ( 'Report1', 'Report1'[Month] = mymonth ), 'Report1'[Volume] )
When you use
FILTER('Report1','Report1'[Month]=[LastMonth])
this returns the whole table instead of the Filtered Table with the LastMonth.
Why?
Because MEASURE is assessed for each ROW of the TABLEThats why you need to put the value of the MEASURE in a VARIABLE first
To avoid returnning a table I put the first measure as below:
LastMonth= CALCULATE(LOOKUPVALUE ('Table1'[Month], 'Table 1'[Month#], MAX('Table1'[Month#]), ALL( Table1))
and put a filter in the report level filter, to exclude "Dec" from the month column in Table1.
But again this is not working! because:
1. LastMonth mesure avoids the filter on the month column and returns "Dec" instead of "Nov".
2. Even when LastMonth meausre returns "Dec", the second measure TotalVolume does not work properly with referring to the first measure.
Did you revise the Total Volume formula as per below?
TotalVolume = VAR mymonth = [LastMonth] RETURN COUNTAX ( FILTER ( 'Report1', 'Report1'[Month] = mymonth ), 'Report1'[Volume] )Yes I did and tested it for my real data, so yes it is working. Thank you very much.
