I need to create a dynamic table that filters certain records from the original transactional table based on a pre-defined date selection. The transactional table (vwProcurementDashboard
) is structured like:
import_date | group | quantity | value |
---|---|---|---|
2021-01-01 | A | 100 | 10 |
2021-01-01 | B | 100 | 15 |
2021-01-02 | A | 110 | 10 |
2021-01-02 | B | 110 | 15 |
Now, I want to filter records using slicer (I) last one month, (II) last 3 months, etc. for which I have created one configuration table (cfg_date_group_frequency
) as below:
I've created the following additional measures to filter the data as below:
dynMonthValue =
// get the number value that needs to be subtracted to create a data filter
SWITCH(
TRUE(),
SELECTEDVALUE(cfg_date_group_frequency[DateGroupName]) == "Last 1 Month", 0,
SELECTEDVALUE(cfg_date_group_frequency[DateGroupName]) == "Last 3 Months", 2,
SELECTEDVALUE(cfg_date_group_frequency[DateGroupName]) == "Last 6 Months", 5,
SELECTEDVALUE(cfg_date_group_frequency[DateGroupName]) == "Last 12 Months", 11,
12 // this is the default number of data that should be returned
)
avlMaxDate =
// get the latest available date from the vwAnalyticsDashboard
MAX(vwProcuementDashboard[import_date])
dynFromDate =
// calculate the date from which the data needs to be filtered
VAR SelectedMonth = MONTH([avlMaxDate]) - [dynMonthValue]
VAR SelectedYear = YEAR([avlMaxDate]) - IF(SelectedMonth < 1, 1, 0)
RETURN
DATE(SelectedYear, IF(SelectedMonth < 1, 12 + SelectedMonth, SelectedMonth), 1)
dynFiltProcurementDashboard =
// dynamically fetch only the selected times from the table
RETURN
FILTER(
vwProcuementDashboard,
vwProcuementDashboard[import_date] >= [dynFromDate]
)
However, when I check the data MIN(dynFiltProcurementDashboard[import_date])
the records are not filtered. However, I have checked separately that all the other values are correctly populated.
As far as I have seen, this is a very standard and straightforward approach, but I am not sure what I am doing wrong. My questions:
EDIT: Please find a sample masked .pbix
file: https://github.com/code-archived/SO-77125934
I think I understand. If you're asking why your calculated table is not reacting to slicers, then it is because calculated tables are evaluated once only at refresh time. They won't respond to slicers or anything other than what you put in the table definition.