Forum Discussion

PaulDenne's avatar
PaulDenne
Helper I
2 years ago
Solved

table filter

Hi I am new to Power bi I have a table filtering weekly hours for all staff from daily timesheets into weeks using a date table to get week numbers and sum the hours for the week, and currently hig...
  • lbendlin's avatar
    lbendlin
    2 years ago

    Use a "What-if"  numeric parameter for that.

PaulDenne's avatar
PaulDenne
Helper I
2 years ago

so I have data below in a table

NAME:  hours worked 

                    WK1        wk 2        wk 3

xxx                 50            65           50  

 

want to filter a table to show rows where any column in it is over 55 for hours worked

NameCurrent week Sum of Regular Hours      
ttrob Grxyyxthd42                      48.81     
ttrob Grxyyxthd44                      56.83     
ttrob Grxyyxthd4220.2     
tbzxrthwtb Htddtb43                      12.00     
tbzxrthwtb Htddtb44                         3.00     
tztw Gutdt4223.37     
tztw Gutdt43                      36.08     
tztw Gutdt44                      46.46     
tztw Ptrtrxzgt42                      36.62     
tztw Ptrtrxzgt43                      34.45     
tztw Ptrtrxzgt4448.98     
tztlt Pxtwtb4247.91     
tztlt Pxtwtb43                      50.87     
tztlt Pxtwtb4447.88     
tztlt Pxtwtb43                      11.02     
tzrxtb Woollty4246.24     
tzrxtb Woollty43                      48.80     
tzrxtb Woollty4447.69     
tltb Htywtb4223.51     
tltb Htywtb43                      24.10     
tltb Htywtb43                      11.75     
tltb Htywtb4424.2     
tltb Jtyytry42                      20.12     
tltb Jtyytry44                      20.40     
tltb Wxlkxbd42                      20.02     
tltb Wxlkxbd43                      20.50     
tltb Wxlkxbd44                      20.25     
tltx Kbxght42                      32.55     
tltx Kbxght43                      31.15     
tltx Kbxght4441.77     
twtbzt tttob42                      36.00     
twtlxt Cltrkt42                      22.00     
twtlxt Cltrkt43                      23.02     
twtlxt Cltrkt44                      44.20     
twjtz tlx42                      40.50     
twjtz tlx43                      37.17     
twjtz tlx44                      11.83     
twy wttchtw42                      12.35     
twy wttchtw43                      48.35     
tbzrt Browb42                      27.83     
tbzrt Browb43                      19.07     
tbzrt Browb44                         2.12     
tbzrtw Grxwtd42                      52.35     
tbzrtw Grxwtd43                      51.22     
tbzrtw Grxwtd44                      36.85     
tbzrtw Wtlburtob4422.63     
tbzrtw Wtltob42                      12.00     
tbzrtw Wtltob44                      12.50     
tbzy dwxth4222.05     
tbzy dwxth43                      35.05     
tbzy dwxth44                      11.03     
tbgtlt Ltwrtbct42                      39.86     
tbgtlt Ltwrtbct43                      23.31     
tbgtlt Ltwrtbct44                      24.42     
tbgtlt dttvtbtob42                      47.68     
tbgtlt dttvtbtob42                      11.25     
tbgtlt dttvtbtob43                      43.39     
  • PaulDenne's avatar
    PaulDenne
    Helper I
    2 years ago

    thanks for that, I think i am so close but when I create the work limit table and the measuer then add slicer I dont get the single value option under slicer settings ?

     

    thanks for help so far really appreciated

    • PaulDenne's avatar
      PaulDenne
      Helper I
      2 years ago

      think I see the issue my work table didnt generate the 30-60 range

      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago

        Use a "What-if"  numeric parameter for that.

  • PaulDenne's avatar
    PaulDenne
    Helper I
    2 years ago

    I know my downfall, the sample I sent you was the total hours for the week,  but my table builds that total byr sumarising daily regular hours, summarised to that weekly total, so the show filter works perfectly if I say put in 15hrs  it will only show me weeks with any one day in the week range that is over 15, I just need to figure out working it to look at the weekly total

  • PaulDenne's avatar
    PaulDenne
    Helper I
    2 years ago

    Hi solved the last step by creating a Measure for Weekly hours current week

    Weekly Hours = CALCULATE(SUM('Detailed - Timesheet Report'[Regular Hours]), ALLEXCEPT('Detailed - Timesheet Report', 'Detailed - Timesheet Report'[Name], 'Date'[Current week]) )
     
    then populated Table with that measure
     
    then created a filter based on your example 
    Filter Weekly hours = if(MAXX('Detailed - Timesheet Report',[Weekly Hours])>('Work Limit'[Work Limit Value]),1,0)
     
    so greatful for your help
    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

      You could also have included the week in the SUMMARIZE.