Forum Discussion

dPramanik's avatar
dPramanik
New Member
2 years ago

Dynamically Filter Table using DAX and SELECTEDVALUE() Function

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_dategroupquantityvalue
2021-01-01A10010
2021-01-01B10015
2021-01-02A11010
2021-01-02B11015

 

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 

4 Replies

    • dPramanik's avatar
      dPramanik
      New Member

      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?

  • Anonymous's avatar
    Anonymous
    Not applicable

    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. 

     

  • Anonymous's avatar
    Anonymous
    Not applicable

    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.