Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

calculate function not giving correct answer

I have a VERY simple formula:   MaxMonth = calculate(max('Fact Table'[CurrentMonth]), filter('Fact Table', max('Fact Table'[RefNum])))   I need it to find the max "RefNum" and lookup the value i...
Need Help
  • d_gosbell's avatar
    d_gosbell
    7 years ago

    If you just want to lookup the value for the max RefNum do you need to a max of the CurrentMonth column or could you just use the LookupValue function?

     

    MaxMonth = LookupValue(,'Fact Table', 'Fact Table'[CurrentMonth]), max('Fact Table'[RefNum]))

    Or if you have mulitple rows with the same RefNum you might need to do something like the following:

     

    MaxMonth =
    VAR _maxRefNum = max('Fact Table'[RefNum])
    RETURN CALCULATE( MAX( 'Fact Table'[CurrentMonth]), 'Fact Table'[RefNum] = _maxRefNum )

  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi Anonymous ,

     

    You could display the returned result of below measure in a card visual.

    MonNo for max refNo =
VAR Curr_Date =
    TODAY ()
VAR Last_day_of_currMon =
    EOMONTH ( Curr_Date, 0 )
RETURN
    IF (
        Curr_Date < Last_day_of_currMon,
        MONTH ( EOMONTH ( Curr_Date, -1 ) ),
        MONTH ( Last_day_of_currMon )
    )

    Best regards,

    Yuliana Gu

Anonymous's avatar
Anonymous
Not applicable
7 years ago

The lookupvalue function worked perfectly, THANK YOU!  I'm still a little unsure as to why the calculate function doesn't work though...

d_gosbell's avatar
d_gosbell
Super User
7 years ago
Anonymous wrote:

The lookupvalue function worked perfectly, THANK YOU!  I'm still a little unsure as to why the calculate function doesn't work though...

The problem with the original calculate is the call to FILTER(). Filter expects 2 parameters, a table to iterate over and a boolean expression, but your call to it looked like the following

 

 filter('Fact Table', max('Fact Table'[RefNum]))

 

With a table and a numeric expression. What happens in this case is that the forumla engine attempts to do an implied cast on max('Fact Table'[RefNum]) to convert the number to a boolean value. And the way it does this is to treat values of 0 as false and non-zero as true. So this will effectively not filter any rows.

 

You should be able to fix this by comparing the current value of the [RefNum] with the max value by adding the following bold code.

 

MaxMonth = calculate(max('Fact Table'[CurrentMonth]), filter('Fact Table', 'Fact Table'[RefNum] = max('Fact Table'[RefNum])))