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.

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

       

    • Whitewater100's avatar
      Whitewater100
      Solution Sage
      4 years ago

      Hello:

      Can you give this a try?

      Max Pop Date =
      var popct = MAX(DataVentures_ALL[Pop_Count])
      return
      MAXX(FILTER(ALL(DataVentures_ALL),
      DataVentures_ALL[Pop_Count] = popct),
      DataVentures_ALL[Date])
       
      I saw some other things that could help you get better/faster results. It would mean changing the data model. Not sure if you want that input?
      Take care..
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        tamerj1 Whitewater100 thank you both so much for your detailed responses, much appreciated. I haven't heard back from the client who owns the file/project. Yes I agree the data model is a bit complex and messy and requires some tweaking to make it more efficient. 

         

        I'll follow up again here if I hear back from the client to resume work on the project. Many thanks again for your help.