Referring A Measure inside Another Measure

[Ynew]

Hi,

I have a problem with referring a measure in other measures. It seems that my second measure does not understand the reference, so could you generally explain a little bit about it?

And here is my specific question.

I have two tables, Table1 and Report1.

What if "Month" column in Table1 is like:

Month:

Jul

Aug

Sep

Oct

Nov

Dec

So this way Month column is sorted based on the dates. I have to filter data to not to include “Dec. And I want to select the "Nov" as the last month and count the volumes for last month which is in another table (Report1).

I used to solve this problem by adding a column with number in "Table1" in it as below:

Month#    Month    

1               Jul

2               Aug

3               Sep

4               Oct

5               Nov

6               Dec

And my other table named "Report1"

Report1:

Volume    Month    

4              Jul

3              Jul

2              Aug 

7              Aug

9              Aug

3              Sep

7              Oct

5              Oct

1              Nov

8              Nov

6              Dec

I use a measure to find the last month as:

LastMonth= VLOOKUP ('Table1'[Month], 'Table 1'[Month#], MAX('Table1'[Month#]))

Then use another measure as below:

TotalVolume= COUNTAX(FILTER('Report1','Report1'[Month]=[LastMonth]),'Report1'[Volume])

Measure "LastMonth" works fine itself. However, it is not working when referred in the second measure. 

I searched and see that VLOOKUP function returns a single value but do not know why its value is not working as a text value in another measure. 

I appreciate if you share your ideas to fix my problem. Thank you very much..

[Zubair_Muhammad · Accepted solution]

HI @Ynew

Please try this MEASURE

TotalVolume =
VAR mymonth = [LastMonth]
RETURN
    COUNTAX ( FILTER ( 'Report1', 'Report1'[Month] = mymonth ), 'Report1'[Volume] )

[Ynew]

Hi @Zubair_Muhammad

Thank you for your help.

Could you please send me the power BI code?

[Zubair_Muhammad]

@Ynew

See the attached PBIX file here

[Ynew]

@Zubair_Muhammad

Thank you very much. 

As I compare, I see that yours is the same as mine. But still cannot understand why mine is not working! if it is related to Power BI version, or...! 

[Zubair_Muhammad]

@Ynew

Is my formula working the way you want?

[Zubair_Muhammad]

@Ynew

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

[Ynew]

@Zubair_Muhammad

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. 

[Zubair_Muhammad]

@Ynew

Did you revise the Total Volume formula as per below?

TotalVolume =
VAR mymonth = [LastMonth]
RETURN
    COUNTAX ( FILTER ( 'Report1', 'Report1'[Month] = mymonth ), 'Report1'[Volume] )

[Ynew]

@Zubair_Muhammad

Yes I did and tested it for my real data, so yes it is working. Thank you very much.

[Zubair_Muhammad]

@Ynew

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 TABLE

Thats why you need to put the value of the MEASURE in a VARIABLE first

[Ynew]

@Zubair_Muhammad

It partially does, but I do not want to have the month (to not to include Dec) as a report level filter not as an slicer.