Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I am trying to calculate the count of Days a User worked at least 1 hour while ignoring a specific page filter.
Each Day should only be counted once. Other Filters should still work.
Here is my Measure:
WorkedDays =
CALCULATE(
COUNTROWS(
SUMMARIZE(
FILTER(TABLEA, TABLEA[WorkedHours] > 0),
TableA[Date]
)
),
ALL(TABLEA[TESTFILTER]), // Remove filter effect of TESTFILTER
ALLSELECTED(TABLEA),
FILTER(TABLEA, TABLEA[WorkedHours] > 0)
)
When I set a TESTFILTER to Green it drops my total for Worked Days but should not.
I need this value to be static so that I can find the average Widgets Produced for all days worked.
Here is a sample table and expected results:
User | Date | Widgets Made | Hours Worked | TestFilter |
1001 | 8/1/2023 | 1 | 1 | Green |
1001 | 8/1/2023 | 1 | 1 | Red |
1002 | 8/1/2023 | 1 | 1 | Green |
1002 | 8/2/2023 | 1 | 1 | Green |
Results with no Filter | ||
User | Total Days Worked | Total Widgets |
1001 | 2 | 2 |
1002 | 2 | 2 |
Desired Results (Page Filter on TestFilter = Green) | |||
User | Days Worked | Total Widgets | AVG Widgets August |
1001 | 2 | 1 | 0.5 |
1002 | 2 | 2 | 1 |
Currently w/ the page filter set I get 1 Day worked for 1001
Thank you for any assistance!
Solved! Go to Solution.
@adoster try these measures:
Days Worked = CALCULATE ( COUNTROWS ( 'Table' ), REMOVEFILTERS ( 'Table'[TestFilter] ) )
Total Widgets = SUM ( 'Table'[Widgets Made] )
Avg Widgets = DIVIDE ( [Total Widgets], [Days Worked] )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@adoster beautiful, good to hear. Cheers!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@adoster try these measures:
Days Worked = CALCULATE ( COUNTROWS ( 'Table' ), REMOVEFILTERS ( 'Table'[TestFilter] ) )
Total Widgets = SUM ( 'Table'[Widgets Made] )
Avg Widgets = DIVIDE ( [Total Widgets], [Days Worked] )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@adoster it will easier if you can share sample pbix using one drive/google drive and the expected output. Without knowing your data model and required output, it is very hard to decode the DAX expression.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hello,
I edited the original post and included sample data.
This is a slimmed down version of my original data which I am unable to post.
Thanks!
User | Count |
---|---|
93 | |
86 | |
78 | |
72 | |
66 |
User | Count |
---|---|
115 | |
106 | |
85 | |
65 | |
64 |