Measure showing MIN and MAX of another measure, across a date range

[Anonymous]

I'm trying to create a measure which I can use in visuals to label the highest and lowest values of another measure across a filtered date range, ultimately like this: 

I'm unable to get the right DAX to do this, and I've tried various approaches using MINX / MAXX with ALLSELECTED or SUMMARIZE and ADDCOLUMNS (because SUMMARIZECOLUMNS didn't work)

Below is the kind of result I'd like the measure to deliver (included Sum as I might use that too, and I assume I'd just use a SUM / SUMX):

(I know I'll need to filter the results so that I don't have the min / max in every row)

For the selected date range and only for the dates that have a non-blank value for the underlying measure, I'd like to calculate the min and max values across the range

I use date hierarchies, so I need to factor the context filter effect of the additional columns. In this case, I've shown that the week of November 30 actually spans two calendar months

Based on previous answers to similar questions, this is the closest I got but it returns the wrong result, in red above

I assume I need to remove filters in some way using ALLSELECTED, so I've tried using it (inside CALCULATE) in the table and in the MAXX steps but none of these give the right result

Any help much appreciated!

[mahoneypat]

Glad you got it working.  What was the final measure that worked?

Regards,

Pat

[Anonymous · Accepted solution]

I'll write it up in an article, maybe here and on Medium

Basically I used the same measures but added some SWITCH logic to avoid wrong results

[Anonymous]

Hi @Anonymous ,

I'm glad to hear that your problem has been solved. And thanks for sharing. Could you please mark your post as Answered? It will help others in the community find the solution easily if they face the same problem with you. Thank you.

Best Regards

[mahoneypat]

Just to clarify, are all of the columns in the visual (not the measures) included in the ALLSELECTED?  If not, please add whatever one is missing.  Also, if you share a link to the pbix (or a mock up of same issue), I can troubleshoot directly.

Regards,

Pat

[Anonymous]

Hi @mahoneypat  - thanks again for the help. I got it to work as desired in the end so didn't upload anything, but I appreciate the offer!

[CNENFRNL]

Hi, @Anonymous , I think you're just one inch away from you goal; you might want to try

Max of measure = MAXX( ALLSELECTED( 'Calendar'[Date] ), [measure] )

Min of measure = MINX( ALLSELECTED( 'Calendar'[Date] ), [measure] )

[Anonymous]

Hi @CNENFRNL - thanks for the reply, I appreciate the help

That logic returns the maximum daily value of the measure rather than weekly...have noted in my other reply the potential way forward though

Appreciate the help nonetheless

[mahoneypat]

Please try this measure pattern to get your desired result.  Change column/table names as needed.

Show Min Max Only =
VAR vThisResult = [Your Measure]
VAR vSummary =
    ADDCOLUMNS (
        ALLSELECTED (
            Date[Year],
            Date[Week]
        ),
        "cResult", [Your Measure]
    )
VAR vMaxResult =
    MAXX (
        vSummary,
        [cResult]
    )
VAR vMinResult =
    MINX (
        vSummary,
        [cResult]
    )
RETURN
    IF (
        OR (
            vThisResult = vMaxResult,
            vThisResult = vMinResult
        ),
        vThisResult,
        BLANK ()
    )

[Anonymous]

Hi @mahoneypat 

Thanks for your answer to help me out - appreciate it!

I tried that logic but I think the presence of the month column in the table visual creates a filter context that causes the formula to return the max weekly value per month rather than the max weekly value across all months

If I add the month column to the ALLSELECTED filter so that the evaluation context isn't grouped per month, then I don't quite get what I want - but maybe it's impossible and I just have to remove the month column

This is without the month column

...which is almost fine except it creates a row for dates that don't have a value for the underlying measure

I could use if(not(isblank as a step to only populate the rows that do have an underlying measure value...perhaps that's the best solution

Thanks again for your tip - will go with that unless there's a more effective solution that means the month column can be accommodated (that was my reasoning for using a SUMMARIZE step...but it didn't work)