Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help with Max Function

Hi, I have this Max function that's returning incorrect result. It's returning max date in data instead of date of the maximum count. Example scenario: if selectedvalue [AIC_RTO] = "Ruapehu" then e...
Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi Anonymous,

    There some mistakes.

    First, some filter you lost some filter on this visual. [Latsed month] is 0 applied on visual "Max daily visitor count", but not on "Max date of vistor count".

     

    Then format the maximum date of 6051. You can just use 'DataVentures_ALL' table directly as the context due to it has been filtered by other slicers on this page. DAX you can refer this:

    Max date of vistor count = 
FORMAT (
    CALCULATE (
        MAX ( DataVentures_ALL[Date] ),
        FILTER ( 'DataVentures_ALL', [Pop_Count] = MAX ( DataVentures_ALL[Pop_Count] ) )
    ),
    "dd mmm'yy"
)

     

    I modified your pbix file in the end you can refer.

     

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi Anonymous 

When you say "max [Pop_Count] = 6051.", this number is based on what exactly? What are you slicing by in your report?

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Anonymous 
    I failed to find a way to get "6051" as the maximum Pop_Count for AIC_RTO "Ruapehu" and Pop_Type "Domestic". I actually get "14766" applying manual filter. However, am not sure if I fully understand your requirement but I think the issue might be with ALLSELECTED which in my openion should be replaced with ALLEXCEPT

    Max date of vistor count = 
IF (
    MAX ( DataVentures_ALL[Pop_Count] )
        = CALCULATE (
            MAX ( DataVentures_ALL[Pop_Count] ),
            ALLEXCEPT ( DataVentures_ALL, DataVentures_ALL[AIC_RTO], DataVentures_ALL[Pop_Type] )
        ),
    FORMAT ( MAX ( DataVentures_ALL[Date] ), "dd mmm'yy" )
)