cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AEIOUandY
Frequent Visitor

calculate not respecting its own filter

Hoping I can get some help here. I have a measure using another measure as a filter value to try to find the earliest date for which a column equals the filter value. I'm getting strange behaviour from the peakdate measure, however.

 

I have a table: TABLE, with columns DATECOL and INDICATOR. The date range in this table is not continuous, but spans more than a year,and ends on the current day (and there are data for that current day). 


I have a measure, PEAK, that finds the maximum value of INDICATOR that falls within a date range.

PEAK = calculate(maxx('TABLE', 'TABLE'[INDICATOR]),'TABLE'[DATECOL]>=date(2021,12,01)&&'TABLE'[DATECOL]<=date(2022,03,01))
This measure seems to work fine - finds the peak value in that date range, and I can confirm that by visually inspecting values in the table.

 

I have ANOTHER measure, DATEOFPEAK, that I want to return the earliest date in DATECOL which the PEAK is observed (in this case, it occurs on multiple days). 

DATEOFPEAK = calculate(min('TABLE'[DATECOL]),filter(all('TABLE'),'TABLE'[INDICATOR]=[PEAK]&&'TABLE'[DATECOL]>=date(2021,12,01)&&'TABLE'[DATECOL]<=date(2022,03,01))

 

This will not work, it returns the minimum date specified by the filter (2021,12,01) rather than the minimum date at which the PEAK value is observed in the INDICATOR column. I have ensured that the variables are formatted in the same way. In the original table (which is a calculated table, although I'm having the same problem whether my PEAK measure is based on a preexisting column, a calculated column, or a column in a calculated table), INDICATOR is rounded to 0 (in the actual column calculation in the summarize DAX command). 

I've tried swapping firstdate for min and I've tried restructuring as a MINX, and everything just returns the minimum date value specified in the PEAK measure but it's ignoring the part of the filter that directs it to filter INDICATOR down to only values that match PEAK.

 

Please, what am I missing?

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

You could try

DATEOFPEAK =
SELECTCOLUMNS (
    TOPN (
        1,
        FILTER (
            ALL ( 'TABLE' ),
            'TABLE'[DATECOL] >= DATE ( 2021, 12, 1 )
                && 'TABLE'[DATECOL] <= DATE ( 2022, 3, 1 )
        ),
        [PEAK], DESC,
        'TABLE'[DATECOL], ASC
    ),
    "@val", 'TABLE'[DATECOL]
)

View solution in original post

2 REPLIES 2
johnt75
Super User
Super User

You could try

DATEOFPEAK =
SELECTCOLUMNS (
    TOPN (
        1,
        FILTER (
            ALL ( 'TABLE' ),
            'TABLE'[DATECOL] >= DATE ( 2021, 12, 1 )
                && 'TABLE'[DATECOL] <= DATE ( 2022, 3, 1 )
        ),
        [PEAK], DESC,
        'TABLE'[DATECOL], ASC
    ),
    "@val", 'TABLE'[DATECOL]
)

A colleague ended up resolving this by putting a variable definition for the PEAK in the DATEOFPEAK calculation rather than referring to it as a measure, and that one change allowed PBI to properly return the DATEOFPEAK value. 

 

Thus, OUR solution was:
PEAKDATE = 
var PEAK = calculate(maxx('TABLE','TABLE'[INDICATOR],datesinperiod('TABLE'[DATECOL],date(2022,03,01,-91,day))

return calculate(min('TABLE'[DATECOL]),filter['TABLE','TABLE'[INDICATOR]=PEAK&&'TABLE'[DATECOL]>=date(2021)&&tab,12,01)&&'TABLE'[DATECOL]<=date(2022,03,01)))

 

The calculation you provided works smoothly and does not require me to prepopulate a variable, however, so I think it's cleaner. Thank you so much for providing this option - I'll be checking for calculation speed and probably changing my definitions to match your solution. 

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