Dynamically Filter Table using DAX and SELECTEDVALUE() Function

[dPramanik]

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:

1. Why is the table not filtered?
2. Is there a better way to achieve this?

I've posted the same question in StackOverflow Community but have not received any feedback yet! Sample database and .pbix file is available here at https://github.com/ZenithClown/SO-77125934 

[v-tangjie-msft]

Hi @dPramanik ,

Has your problem been solved? If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out. Thanks in advance.

Best Regards,

Neeko Tang

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

[v-tangjie-msft]

Hi @dPramanik ,

Unfortunately Power BI Desktop does not support the creation of dynamic tables at this time.

If you would like to suggest  feature improvements, you may vote the idea and comment here to improve this feature. It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

You can present data as table visual objects. Use visual object filtering instead of creating a filter table.

Flag = IF(MAX('vwProcuementDashboard'[import_date]) >= [dynFromDate],1,0)

Best Regards,

Neeko Tang

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

[devesh_gupta]

@dPramanik Try out these approaches:

https://medium.com/chandakamit/power-bi-when-i-felt-lazy-and-i-needed-too-many-measures-ed8de20d9f79

https://community.powerbi.com/t5/Desktop/Required-custom-date-Slicer-Last-7-days-last-15-days-last-3...

If it helps, please mark it as an accepted solution so that other users can find it more easily.

[dPramanik]

@devesh_gupta Thanks for the link, however, this approach is not feasible for me as I have to do multiple levels of group by and ranking on the filtered result. I've also attached the link to sample files, let me know if you can check on it?