topic Re: table filter in DAX Commands and Tips

table filter

PaulDenne — Thu, 02 Nov 2023 07:36:48 GMT

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 highlights any week over 50, and a slider to select which week range they want to look at

Hours worked

Name Week 1 week 2

xxxxx 45 54

I want to apply a filter to just show any row that has at least one week over 50 in the range selected, but I get stuck it flters by value of all weeks shown added together

many thanks

Re: table filter

lbendlin — Sat, 04 Nov 2023 15:00:39 GMT

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: table filter

PaulDenne — Wed, 08 Nov 2023 07:48:31 GMT

Hi Ibendlin

did you get my reply and sample data, only noticed its not swing on the topic thread?

Re: table filter

lbendlin — Wed, 08 Nov 2023 12:40:45 GMT

No. Please try again.

Re: table filter

PaulDenne — Thu, 09 Nov 2023 14:30:14 GMT

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

Name	Current week	Sum of Regular Hours
ttrob Grxyyxthd	42	48.81
ttrob Grxyyxthd	44	56.83
ttrob Grxyyxthd	42	20.2
tbzxrthwtb Htddtb	43	12.00
tbzxrthwtb Htddtb	44	3.00
tztw Gutdt	42	23.37
tztw Gutdt	43	36.08
tztw Gutdt	44	46.46
tztw Ptrtrxzgt	42	36.62
tztw Ptrtrxzgt	43	34.45
tztw Ptrtrxzgt	44	48.98
tztlt Pxtwtb	42	47.91
tztlt Pxtwtb	43	50.87
tztlt Pxtwtb	44	47.88
tztlt Pxtwtb	43	11.02
tzrxtb Woollty	42	46.24
tzrxtb Woollty	43	48.80
tzrxtb Woollty	44	47.69
tltb Htywtb	42	23.51
tltb Htywtb	43	24.10
tltb Htywtb	43	11.75
tltb Htywtb	44	24.2
tltb Jtyytry	42	20.12
tltb Jtyytry	44	20.40
tltb Wxlkxbd	42	20.02
tltb Wxlkxbd	43	20.50
tltb Wxlkxbd	44	20.25
tltx Kbxght	42	32.55
tltx Kbxght	43	31.15
tltx Kbxght	44	41.77
twtbzt tttob	42	36.00
twtlxt Cltrkt	42	22.00
twtlxt Cltrkt	43	23.02
twtlxt Cltrkt	44	44.20
twjtz tlx	42	40.50
twjtz tlx	43	37.17
twjtz tlx	44	11.83
twy wttchtw	42	12.35
twy wttchtw	43	48.35
tbzrt Browb	42	27.83
tbzrt Browb	43	19.07
tbzrt Browb	44	2.12
tbzrtw Grxwtd	42	52.35
tbzrtw Grxwtd	43	51.22
tbzrtw Grxwtd	44	36.85
tbzrtw Wtlburtob	44	22.63
tbzrtw Wtltob	42	12.00
tbzrtw Wtltob	44	12.50
tbzy dwxth	42	22.05
tbzy dwxth	43	35.05
tbzy dwxth	44	11.03
tbgtlt Ltwrtbct	42	39.86
tbgtlt Ltwrtbct	43	23.31
tbgtlt Ltwrtbct	44	24.42
tbgtlt dttvtbtob	42	47.68
tbgtlt dttvtbtob	42	11.25
tbgtlt dttvtbtob	43	43.39

Re: table filter

lbendlin — Thu, 09 Nov 2023 21:32:39 GMT

Re: table filter

PaulDenne — Thu, 09 Nov 2023 22:21:56 GMT

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

Re: table filter

PaulDenne — Thu, 09 Nov 2023 22:24:28 GMT

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

Re: table filter

lbendlin — Thu, 09 Nov 2023 22:30:54 GMT

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

Re: table filter

PaulDenne — Thu, 09 Nov 2023 22:35:58 GMT

many thanks, didn't cover that in my 1 day tutorial

Re: table filter

PaulDenne — Thu, 09 Nov 2023 22:43:16 GMT

so last step ..hopefully

I replaced names with my tables and when I add the show filter not data comes for any value, so I have messed up somewhere addin the measuer?

Show =

var a = SUMMARIZE('Detailed - Timesheet Report',[Name])

var b = ADDCOLUMNS(a,"mx",max('Detailed - Timesheet Report'[Regular Hours]))

return if(maxx(b,[mx])>=SELECTEDVALUE('Work Limit'[Work Limit]),1,0)

Re: table filter

PaulDenne — Thu, 09 Nov 2023 22:55:20 GMT

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

Re: table filter

PaulDenne — Thu, 09 Nov 2023 23:14:46 GMT

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

Re: table filter

lbendlin — Fri, 10 Nov 2023 00:16:05 GMT

You could also have included the week in the SUMMARIZE.