Forum Discussion
Calculation Filter working when using Today Function, but not when I use a calculated date
Hi again cjbaguley
Thanks for sharing the PBIX!
I had a look and the measure itself actually seemed to be working correctly, however some visual interactions were disabled in the report page, which meant the card visual wasn't updating based on filters.
Nonetheless, I would recommend these other adjustments which I've made in the attached PBIX:
1. Added columns Offset Min and Offset Max to Lender Start Date Slicer:
2. Rewrote Lender Period Count to make use of these columns:
Lender Period Count = VAR SelectedDate = [Selected Date] VAR SingleFilter = HASONEFILTER ( 'Lender Start Date Slicer'[Days Since Start Date] ) VAR OffsetMin = IF ( SingleFilter, SELECTEDVALUE ( 'Lender Start Date Slicer'[Offset Min] ), -365 ) VAR OffsetMax = IF ( SingleFilter, SELECTEDVALUE ( 'Lender Start Date Slicer'[Offset Max] ), 0 ) RETURN CALCULATE ( COUNT ( 'Lender Details'[LenderEmpNoText] ), KEEPFILTERS ( 'Lender Details'[Lender Role Start Date] >= SelectedDate + OffsetMin && 'Lender Details'[Lender Role Start Date] <= SelectedDate + OffsetMax ) ) + 0The measure will apply a filter according to the single date range selected. If there are multiple or no selections made, the default offset is [-365,0].
3. I also added a visual-level filter to the table visual: Lender Period Count ≠ 0.
This seems to work as intended. Is this what you're looking for?
Hi cjbaguley
The short explanation is that [SELECTED DATE] is being evaluated in the row context of 'Lender Details' iterated by FILTER. Each time it is evaluated, the "current row" of 'Lender Details' is converted to an equivalent filter which is applied when evaluating [SELECTED DATE] for that row.
However you want [SELECTED DATE] to be evaluated once and used within the Lender Role Start Date filter.
To fix this, I would recommend storing [SELECTED DATE] in a variable, and also applying filters on the Lender Role Start Date column within KEEPFILTERS rather than filtering the 'Lender Details' table. Generally it is best to filter columns rather than tables.
VAR days_60 =
VAR SelectedDate = [SELECTED DATE]
RETURN
CALCULATE (
COUNT ( 'Lender Details'[LenderEmpNoText] ),
KEEPFILTERS ( 'Lender Details'[Lender Role Start Date] >= SelectedDate - 60
&& 'Lender Details'[Lender Role Start Date] <= SelectedDate - 31 )
)Does an expression like this fix things?
Some relevant articles:
https://www.sqlbi.com/articles/understanding-context-transition-in-dax/
https://www.sqlbi.com/articles/context-transition-in-dax-explained-visually/
https://www.sqlbi.com/articles/filter-columns-not-tables-in-dax/
Thanks OwenAuger,
That really helped me understand the context of what I'm looking to do.
I've tried amending the whole measure using your methodology laid out, however I still seem to be running into issues. To add further context, the measure is using a table of (1-30 Days, 31-90days etc) which is in a slicer. If no slicer selection is made, it should return all values that are within 365 days before the selected date, otherwise apply the appropriate time banding for the slicer selection.
I have tried storing the 'selecteddate' variable both as each date banding, and also as a top line variable within the measure.
This is the full measure as I have it now, but its not returning any values whether filtered or unfiltered:
I would try debugging each "sub-measure" by creating individual measures such as this
days_30 measure = CALCULATE ( COUNT ( 'Lender Details'[LenderEmpNoText] ), KEEPFILTERS ( 'Lender Details'[Lender Role Start Date] >= SelectedDate - 30 && 'Lender Details'[Lender Role Start Date] <= SelectedDate ) )and testing in a simple visual.
There is likely some combination of filters resulting in blank measures. Could you share a sample pbix?
I would also suggest using a dynamic segmentation approach rather than repeating the code:
https://www.daxpatterns.com/dynamic-segmentation/Thanks again Owen, apologies for the delay in reply, long weekend here and other priorities this week.
I think I have been able to replicate my pbix properly without real data and uploaded ot my dropbox Sample Lender Details pbix
I did try the debugging you suggested above, and while I could get it to work filtering anything >= Min(Date) (showing all 23 sample lenders, when I tried to do <= Min(Date) I get blanked out, when I'm expecting it to show only the 7 lenders who meet that criteria.Hi again cjbaguley
Thanks for sharing the PBIX!
I had a look and the measure itself actually seemed to be working correctly, however some visual interactions were disabled in the report page, which meant the card visual wasn't updating based on filters.
Nonetheless, I would recommend these other adjustments which I've made in the attached PBIX:
1. Added columns Offset Min and Offset Max to Lender Start Date Slicer:
2. Rewrote Lender Period Count to make use of these columns:
Lender Period Count = VAR SelectedDate = [Selected Date] VAR SingleFilter = HASONEFILTER ( 'Lender Start Date Slicer'[Days Since Start Date] ) VAR OffsetMin = IF ( SingleFilter, SELECTEDVALUE ( 'Lender Start Date Slicer'[Offset Min] ), -365 ) VAR OffsetMax = IF ( SingleFilter, SELECTEDVALUE ( 'Lender Start Date Slicer'[Offset Max] ), 0 ) RETURN CALCULATE ( COUNT ( 'Lender Details'[LenderEmpNoText] ), KEEPFILTERS ( 'Lender Details'[Lender Role Start Date] >= SelectedDate + OffsetMin && 'Lender Details'[Lender Role Start Date] <= SelectedDate + OffsetMax ) ) + 0The measure will apply a filter according to the single date range selected. If there are multiple or no selections made, the default offset is [-365,0].
3. I also added a visual-level filter to the table visual: Lender Period Count ≠ 0.
This seems to work as intended. Is this what you're looking for?