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

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. 

    • Whitewater100's avatar
      Whitewater100
      Solution Sage
      4 years ago

      Good am:

      When filtering down to your requirment on the DATAVENTURES_ALL I believe theresult was for 1-1-2022 with 651 count. I think that was solved? 

       

      If you can find a way to slide a date table in and get the facts and dims squared away, you should be in great shape!

       

      Thanks!

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Whitewater100 , agree the moded needs a date table and refinement but I'll leave that for the client to decide.

        I did manage to resolve the issue based on current model by tweaking the formula based on the responses here including yours. 

        So again, many thanks for your kind efforts and help with this, much appreciated.