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, thanks for the prompt response.

 

How would I have these as seperate measures for each 2020 and 2022? 

Would like to show them in 2 instances

Anonymous's avatar
Anonymous
Not applicable
6 years ago

what measure  are you expecting?

 

I will give one example of count.

 

measure1=Calculate(Count(Table[Column]),filter(table,Table[year]=2020))

measure2=Calculate(Count(Table[Column]),filter(table,Table[year]=2022))

 

 

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 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

    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      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.

    • 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