How does CALCULATE Filter when name in filter does not match any table column

[alsm]

Hello All,

I have a piece of DAX that works...I just do not get it why. Was I simply lucky or is this the way.

I have two related tables

Header Table
HeaderKey	DetailsKey	ValidFrom	ValidTo
H1	D1	1Jan2022	31Dec2022
H2	D2	15Feb2022	31Oct2022
H3	D3	10Mar2022	15Sep2022
H4	D4	7Feb2022	25Feb2022
H5	D5	25Feb2022	14Apr2022
H6	D6	12Jan2022	12Nov2022
H7	D7	24Feb2022	24Feb2023
H8	D8	16Mar2022	16Oct2022

Limit Table
HeaderKey	LimitVolume	ValidFrom	ValidTo
H1	500	1Jan2022	15Feb2022
H1	450	15Feb2022	31Mar2022
H1	400	1Apr2022	31Dec2022
H2	700	15Feb2022	1Mar2022
H2	550	1Mar2022	31Oct2022
H3	250	10Mar2022	1May2022
H3	250	1May2022	15Sep2022
H4	770	7Feb2022	25Feb2022
H5	800	25Feb2022	14Apr2022
H6	450	12Jan2022	31Mar2022
H6	350	1Apr2022	12Nov2022
H7	600	24Feb2022	24Feb2023
H8	320	16Mar2022	16Oct2022

The volume limits corresponding to the Header keep on changing every month

e.g. in above table H1 volume limits was 500 from 1Jan to 15Feb and hen 450 from 15Feb to 31Mar and finally 400 from 1Apr to 31Dec

H1	500	1Jan2022	15Feb2022
H1	450	15Feb2022	31Mar2022
H1	400	1Apr2022	31Dec2022

I want to show in a table only limits that changed in the selected month (i.e on 31Jan it should show 500, then 28Feb it should 450 but it should NOT show anything for 31Mar as limit did not change in month of March)

The DAX measure I wrote was

ChangedVolumeLimit = 
VAR VolLimFilter = FILTER(VolumeLimitTbl, VolumeLimitTbl[ValidFrom] > [PrevEoMFromSelect] && VolumeLimitTbl[ValidFrom] < [SelectEoM] && VolumeLimitTbl[ValidTo] > [SelectEoM])
VAR VolModifiedThisMonth = SELECTCOLUMNS(VolLimFilter, "hkey", [HeaderKey])

VAR NewHeaderFilter = FILTER(HeaderTbl, HeaderTbl[ValidFrom] > [PrevEoMFromSelect] && HeaderTbl[ValidFrom] > [SelectEoM] && HeaderTbl[ValidTo] > [SelectEoM])
VAR NewHeadersThisMonth = SELECTCOLUMNS(NewHeaderFilter, "newheaders", [HeaderKey])

VAR OnlyModifiedVolsNoNewHeaders = EXCEPT(VolModifiedThisMonth, NewHeadersThisMonth)

VAR NewVolLimit = CALCULATE(MAX(VolumeLimitTbl[LimitVolume]), OnlyModifiedVolsNoNewHeaders)

RETURN
    NewVolLimit

The measure works, the logic being filter all changes in volumes and then remove any new ones (as I want to show only changes in volume or brand new volumes)

My question is related to line 

VAR NewVolLimit = CALCULATE(MAX(VolumeLimitTbl[LimitVolume]), OnlyModifiedVolsNoNewHeaders)

How did this piece of code work? It should have been VolumeLimit[HeaderKey] in {list of valid keys}

Finally, is there a better way do it?

Thank you in advance for your help and guidance

[v-jialluo-msft]

Hi  @alsm ,

If you just want to show the change in the selected month, you can try the following steps.

(1) Create a new measure

FLAG = 
VAR _MONTH = VALUES('CALENDAR'[Month])
VAR _FM = MONTH(MAX('Limit Table'[ValidFrom]))
RETURN IF(ISFILTERED('CALENDAR'[MONTH]),IF(_MONTH = _FM ,1,0),1)

(2)Apply filtering

(3)Final output

Best Regards,
Gallen Luo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.