Forum Discussion

Stabbathehut's avatar
Stabbathehut
Frequent Visitor
6 years ago
Solved

Dynamic filter for year

Hi All, I would like to show documents filtered for differing years dynamically.     So I would like to have a measure which only shows 2020 or 2022. How would I go about this.   Thanks i...
Need Help
Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Stabbathehut 

    Do you add [date] column from a date table, then add"corresponding documents" to the same table visual?

    If so, you could create a relationship between two tables.

    For example, my date table is create as below:

    date table = ADDCOLUMNS(CALENDAR(DATE(2020,1,1),DATE(2022,12,31)),"year",YEAR([Date]),"month",MONTH([Date]))

add a calculated column
firstdayperyear = CALCULATE(MIN('date table'[Date]),ALLEXCEPT('date table','date table'[year]))

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Stabbathehut's avatar
Stabbathehut
Frequent Visitor
6 years ago

Hi I am trying to say, if date = 2020 I would like to show the values corresponding to it. So in the image below it only shows 2020 with the corresponding documents

Anonymous's avatar
Anonymous
Not applicable
6 years ago

NEw measure= If(max(table[year]) in allselected(date[year]),1,0)

 

add it to visual level filter and set it to 1.

 

here date[year] is disconnected date table

new Date table=value(table[YEAR])

 

and add date table column as slicer.

Now work dynamically whatever you select in slicer for e.g 2020 or 2022 or both the values.

 

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

check my blog here
https://community.powerbi.com/t5/Community-Blog/Connecting-to-a-Tabular-Model-Using-Power-BI/ba-p/913784

 

  • Stabbathehut's avatar
    Stabbathehut
    Frequent Visitor
    6 years ago

    Hi there, I am getting an error with New measure = if(max(Sheet1[Next revision date]) in allselected(date[Next revision date])1,0

     

    Did try putting 2020 but invalid DAX also

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      what error are you getting?

       

      If you want to put 2020 in measure you need to add it in Curly brackets "{2020}".

       

      Same measure working in my case.

       

      I am still doubtfull with your question.

       

      Why connected slicers are not working for you.

      If you select 2020 automatically slicer will show 2020 related data.

      If you select 2022 automatically slicer will show 2022 related data.

       

      please be clear about your exact requirement.

       

      Share sample data and exact expected output with 2 3 test cases.

       

      Thanks & regards,
      Pravin Wattamwar
      www.linkedin.com/in/pravin-p-wattamwar

      If I resolve your problem Mark it as a solution and give kudos.

      check my blog here
      https://community.powerbi.com/t5/Community-Blog/Connecting-to-a-Tabular-Model-Using-Power-BI/ba-p/913784