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 expected result = 01/01/2022. Manual check of max [Pop_Count] = 6051.

However current function is returning incorrect result of 31/01/2022

tamerj1 , Whitewater100 appreciate any help please. Thanks.

Max date of vistor count = IF( MAX(DataVentures_ALL[Pop_Count]) = CALCULATE(MAX(DataVentures_ALL[Pop_Count]) ,ALLSELECTED(DataVentures_ALL[AIC_RTO] ,DataVentures_ALL[Pop_Type])) , 

FORMAT(MAX(DataVentures_ALL[Date]) ,"dd mmm'yy" ))

 

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.

12 Replies

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    Hi:

    I'm better with having the data but will take a shot. Once your max measure is working the way you want, then

    Can you try FIRSTNONBLANK OR LASTNONBLANK

    Date of Max = LASTNONBLANK(DataVenture[Date], [put formula for the max here])) -Best Option

    in a more simple example FIRSTNONBLANK(DataVenture[Date], SUM(DataVenture[units]))

    If this doesn't work and you have example data I can check it out a bit later..Hope this helps.

      • Whitewater100's avatar
        Whitewater100
        Solution Sage
        4 years ago

        Hello:

        OK, final got it. Boy, yo have 20 dae tables (in the background) and some serious bi-di, however if you bring your filter over for month = 0 and use this measure it should be good to go. I beleive 7-11-20 is the all-time high but Jan1 2022 is what we get with the filtering with 6051 daily vistors. 

        Max Pop max per Date =
        var maxpop = [Max Pop]
        return
        MAXX(
            KEEPFILTERS(FILTER(DataVentures_ALL, DataVentures_ALL[Pop_Count] = maxpop)),
            DataVentures_ALL[Date])

         

         

        The picture

         

  • 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" )
)


  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Anonymous 
    Here is the sample file with the solution https://www.dropbox.com/t/LfIreNwcUKqWWqSW
    Actually I've noticed that even after modification, the measure still returns the very last date at the grand total level (Which is the level that is displayed at the card visual). Therefore I rewrote the measure as follows

     

    Max date of vistor count = 
Var SelectedTable =
    CALCULATETABLE (
        DataVentures_ALL,
        ALLEXCEPT ( DataVentures_ALL, DataVentures_ALL[AIC_RTO], DataVentures_ALL[Pop_Type] )
    )
VAR MAxCount =
    MAXX ( SelectedTable, DataVentures_ALL[Pop_Count] )
VAR FilteredTable =
    FILTER ( SelectedTable, DataVentures_ALL[Pop_Count] = MaxCount )
RETURN 
    FORMAT ( 
        MAXX ( FilteredTable, DataVentures_ALL[Date] ), 
        "dd mmm'yy"
    )

     

    Also I notied that the card visual that desplays the maximum count is also returning wrong numbers at least as per my understanding (please let me know if I am mistaken). I wrote another measure for that:

     

    MAx daily visitor count = 
CALCULATE (
    MAX ( DataVentures_ALL[Pop_Count] ),
    ALLEXCEPT ( DataVentures_ALL, DataVentures_ALL[AIC_RTO], DataVentures_ALL[Pop_Type] )
)

     

    This is how your dashboard looks like now

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      v-chenwuz-msft Perfect, that worked. Thank you so much for your response and help with this, much appreciated.