Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Dynamically Filter Based on the Value selected

 

Hello,

I want to create two filters for two date columns (one representing the start year and the other representing the end year). I want to ensure that when I select a year in the start year filter, the choices available in the end year filter are greater than or equal to the selected start year. These columns are associated with query parameters. 

 

I tried creating a measure as follows:

YearInitialMin = MIN(YearInitialTable[Years])
IsValidEndYearMeasure =
var is_in_range = CALCULATE(COUNTROWS(YearEndTable),FILTER(YearEndTable,YearEndTable[Years]>=[YearInitialMin]))
return if(is_in_range>0,1,0)
 
Then, I applied this in the filter pane of my second graph, by filtering with IsValidEndYearMeasure is 1. 
 

However, I got this message: An incompatible filter is used on a column with a parameter mapping. 

I would appreciate your help.

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,
    Thanks for amitchandak rerply.
    It looks as if you already know the filtering logic, now you just need to place these two columns in two tables that are not related and create a measure as follows
    Sample data

    IsAfter = 
IF(
    SELECTEDVALUE('End Year'[End Year]) >= SELECTEDVALUE('Start Year'[Start Year]),
    1,
    0
)

    Final output

    Best regards,
    Albert He

    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
      2 years ago

      Thanks for your solutions amitchandak  and Anonymous . I implemented it with some changes related to the needs of my projects (I need to have a specific date format like YYYY-MM). 

       

      However, after creating the measure such as: 

      isAfter = IF(VALUE(LEFT(SELECTEDVALUE('YYYY-MM (Initial)'[YYYY-MM]), 4)) < VALUE(LEFT(SELECTEDVALUE('YYYY-MM (End)'[YYYY-MM]), 4)), 1, 0)
       
      I got the message: An incompatible filter is used with a parameter mapping. The problem is that I use a parameter in my column, as mentioned in my problem, and I think I can't use this type of solutions with this constraint. 
       
      That's why I look for new solution, regarding parameter constraints (limitations on filters are on this link: Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn)