cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
qsmith83
Responsive Resident
Responsive Resident

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

Screenshot 2022-03-31 091035.png

 

1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support

Hi @qsmith83,

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".

vchenwuzmsft_1-1649082744043.png

 

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.

View solution in original post

12 REPLIES 12
v-chenwuz-msft
Community Support
Community Support

Hi @qsmith83,

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".

vchenwuzmsft_1-1649082744043.png

 

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.

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

tamerj1
Super User
Super User

@qsmith83 
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
1.png

tamerj1
Super User
Super User

Hi @qsmith83 

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

@qsmith83 
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" )
)

1.png

Whitewater100
Super User
Super User

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.

Thanks for your quick response @Whitewater100  I tried your solutions but couldn't get it to work.

Here's link to sample file and I've highlighted the card visual referencing the max forumula.

https://drive.google.com/file/d/1M6S5oZ7K2I-4V89q6fmcQSmEo5B3oS5m/view?usp=sharing

Screenshot 2022-03-31 104743.png

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_0-1648747776932.png

 

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..

@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. 

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!

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors