Forum Discussion

Vickram's avatar
Vickram
Icon for Helper III rankHelper III
5 years ago
Solved

when passing the value returned from MAX function, the DAX returns incorrect output

I have a measure which calculates the current year:  Current Year = YEAR(TODAY()). This returns 2020 and I can visualize it in a Card visual.

 

Another measure to calculate max year in my data:  Max Forecast Year = MAX(PopForecastTest[year]). This returns 2041 and this too can be visualized correctly in a Card visual.

 

Now, I create another measure as:

Current year population =
CALCULATE(
SUM('PopForecastTest'[ForecastPop]),
FILTER(ALL(PopForecastTest[year]), PopForecastTest[year]=[Current Year]))
This measure returns correct value.
 
Similarly, I want to dispaly the forecast data at max year i.e. 2041, then I created similar measure as above:
Max Forecast year population =
CALCULATE(
SUM('PopForecastTest'[ForecastPop]),
FILTER(ALL(PopForecastTest[year]), PopForecastTest[year] = [Max Forecast Year]))
Now this measure displays incorrect value. In the above DAX, if I replace [Max Forecast Year] by static value 2041, it displays correct data. Since the measure [Max Forecast Year] returns 2041, then why it does not work when I used in the above DAX. Any help will be well appreciated.
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Vickram , Should have worked. Try this change and check

     

    Max Forecast Year = MAXX(allselected(PopForecastTest),PopForecastTest[year])

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Vickram , Should have worked. Try this change and check

     

    Max Forecast Year = MAXX(allselected(PopForecastTest),PopForecastTest[year])

    • Vickram's avatar
      Vickram
      Icon for Helper III rankHelper III
      5 years ago

      Hi Amit, this works amazingly. What is the difference between MAX that I used and the MAXX you suggested?

      Also, passing the value from measure to a variable then used that in the Filter in the above DAX as suggested by Fowmy also works.

      Thanks to you both for the prompt reply.

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    Vickram 

    Check the following modified measure:

     

    Max Forecast year population =
VAR _MaxYear = [Max Forecast Year] return

CALCULATE(
SUM('PopForecastTest'[ForecastPop]),
FILTER(ALL(PopForecastTest[year]), PopForecastTest[year] = _MaxYear  )
)

     

    Simpler version:

    Max Forecast year population =
    VAR _MaxYear = [Max Forecast Year] return

    CALCULATE(
    SUM('PopForecastTest'[ForecastPop]),
    PopForecastTest[year] = _MaxYear 
    )

     

     

     

     

    ________________________

    If my answer was helpful, please click Accept it as the solution to help other members find it useful

    Click on the Thumbs-Up icon if you like this reply 🙂


    Website YouTube  LinkedIn