Forum Discussion

dan_yoxall's avatar
dan_yoxall
Helper I
9 years ago
Solved

Dynamic filter not working

Hi

 

With the below is an snapshot of the first few of lines of data I am trying to create a line graph of [Cost. Reg] against [Date] but filter the date by [Origin] and [Task].

 

 

I used the below measure and it worked fine (see graph further below).  However I want the [Task] filter to be dynamic.  If I create a slide using the [Task] field and omit the text in bold the filter doesn't appear to work.  Any idea why?

 

Cumulative Spend to Date (Time Sheet) =
     CALCULATE(
            SUMX ( 'Project Hours Booked', 'Project Hours Booked'[Cost, Reg.]) * MAX('Fee Factor'[Fee Factor]),
                  FILTER(
                         ALL('Project Hours Booked'),
                         'Project Hours Booked'[Date] <= MAX('Project Hours Booked'[Date]) &&
                         'Project Hours Booked'[Origin] = "TIME SHEET" &&
                         OR('Project Hours Booked'[Task] = "1000", OR('Project Hours Booked'[Task] = "1100", 'Project Hours Booked'[Task] = "1200"))
         )
)

 

 

 

 

 

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi dan_yoxall,

    Could you please share the incorrect screenshot for further analysis? Please create a slicer including [Task], and create your measure using the formula, check if it works fine.

    Cumulative Spend to Date (Time Sheet) =
     CALCULATE(
            SUMX ( 'Project Hours Booked', 'Project Hours Booked'[Cost, Reg.]) * MAX('Fee Factor'[Fee Factor]),
                  FILTER(
                         ALLSELECTED('Project Hours Booked'),
                         'Project Hours Booked'[Date] <= MAX('Project Hours Booked'[Date]) &&
                         'Project Hours Booked'[Origin] = "TIME SHEET" 
         )
)


    Please don't hesitate to ask if you have other issue.

    Best Regards,
    Angelia

3 Replies

  • dan_yoxall's avatar
    dan_yoxall
    Helper I
    9 years ago

    Realised the first paragraph doesn't make a whole lot of sense!!!  See below corrected version:  

     

    The below is an snapshot of the first few of lines of data I am using to create a line graph of [Cost. Reg] against [Date] which is then filtered by [Origin] and [Task].

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Microsoft Employee
      9 years ago

      Hi dan_yoxall,

      Could you please share the incorrect screenshot for further analysis? Please create a slicer including [Task], and create your measure using the formula, check if it works fine.

      Cumulative Spend to Date (Time Sheet) =
     CALCULATE(
            SUMX ( 'Project Hours Booked', 'Project Hours Booked'[Cost, Reg.]) * MAX('Fee Factor'[Fee Factor]),
                  FILTER(
                         ALLSELECTED('Project Hours Booked'),
                         'Project Hours Booked'[Date] <= MAX('Project Hours Booked'[Date]) &&
                         'Project Hours Booked'[Origin] = "TIME SHEET" 
         )
)


      Please don't hesitate to ask if you have other issue.

      Best Regards,
      Angelia

      • dan_yoxall's avatar
        dan_yoxall
        Helper I
        9 years ago

        Hi Angelia

         

        I used your amended formula with the ALLSELECT function and the slicer now works!

         

        Thanks

        Dan