Forum Discussion
Need Help: Power BI filter with group by
Hi All,
I have an unique screnario which i am at wits end trying to resolve. The data looks like below, where each user has number of hours worked for a week. The hours can have any value greater than 0.
| username | hours | start week | end week |
| A | 40 | 7/4/2021 | 7/10/2021 |
| A | 36 | 7/11/2021 | 7/17/2021 |
| A | 34 | 7/18/2021 | 7/24/2021 |
| A | 30 | 7/25/2021 | 8/1/2021 |
| B | 50 | 7/4/2021 | 7/10/2021 |
| B | 20 | 7/11/2021 | 7/17/2021 |
| B | 20 | 7/18/2021 | 7/24/2021 |
| B | 20 | 7/25/2021 | 8/1/2021 |
| C | 28 | 7/4/2021 | 7/10/2021 |
| C | 32 | 7/11/2021 | 7/17/2021 |
| C | 44 | 7/18/2021 | 7/24/2021 |
| C | 50 | 7/25/2021 | 8/1/2021 |
I want to add a dropdown to the visual where the user can select a value for number of hours(5,10,20, 30,35 etc).
If the latest hour value(based on dates) is greater than the value selected, ignore that user.
If the latest hour value(based on dates) is less than value selected, select user and get end date of period where its higher.
For e.g. if the user selects 35, the output will be :
| end of week | |
| A | 7/17/2021 |
| B | 7/10/2021 |
In the above,
| A | 36 | 7/11/2021 | 7/17/2021 |
row is higher than the 35 hour, so select that row and not the row above.
For B, a similar logic applies.
Please let me know if you have any ideas how to proceed with this. Thanks in advance.
Here is a measure expression that works with your username column in a table visual, and a disconnected table with GENERATESERIES for the slicer.with
SlicerHours = GENERATESERIES(20,50,5)
Latest Week Over Limit =
VAR slicervalue =
MAX ( SlicerHours[Value] )
VAR latesthours =
LASTNONBLANKVALUE ( Hours[end week], CALCULATE ( MAX ( Hours[hours] ) ) )
VAR latestweekabove =
CALCULATE ( MAX ( Hours[end week] ), Hours[hours] >= slicervalue )
RETURN
IF ( latesthours >= slicervalue, BLANK (), latestweekabove )
Pat
2 Replies
- mahoneypat
Microsoft Employee
Here is a measure expression that works with your username column in a table visual, and a disconnected table with GENERATESERIES for the slicer.with
SlicerHours = GENERATESERIES(20,50,5)
Latest Week Over Limit =
VAR slicervalue =
MAX ( SlicerHours[Value] )
VAR latesthours =
LASTNONBLANKVALUE ( Hours[end week], CALCULATE ( MAX ( Hours[hours] ) ) )
VAR latestweekabove =
CALCULATE ( MAX ( Hours[end week] ), Hours[hours] >= slicervalue )
RETURN
IF ( latesthours >= slicervalue, BLANK (), latestweekabove )
Pat