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

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