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.
Hi,
I am new to PowerBI and trying to a measured columns in PowerBI for displaying a Card. I'm trying to divide with a filter, but getting an error (screenshot below). How can I devide with a filtered measure? I created a formula beforehand for the First response (hrs) column.
I tried referencing this link: Divide with a filter
Formula:
Tickets responded = Count of tickets responded under 25 hrs/Count of tickets
Data:
Ticket ID | Created time | Initial response time |
1234 | 2018-04-15 06:14:10 | 2018-04-16 08:44:45 |
1235 | 2018-04-16 00:09:10 | |
1236 | 2018-04-16 02:41:51 | |
1237 | 2018-04-16 02:45:14 | |
1238 | 2018-04-16 07:24:58 | 2018-04-16 07:55:06 |
1239 | 2018-04-16 10:37:07 | 2018-04-16 12:22:33 |
1240 | 2018-04-16 11:21:12 | 2018-04-16 15:41:24 |
1241 | 2018-04-16 11:28:37 | 2018-04-16 15:41:40 |
1242 | 2018-04-16 12:04:47 | |
1243 | 2018-04-16 13:14:37 | 2018-04-17 10:43:21 |
1st formula: First response (hrs) = DATEDIFF('Sheet1 (2)'[Created time], 'Sheet1 (2)'[Initial response time], HOUR)
2nd formula: (not working)
Tickets Responded = DIVIDE(CALCULATE(COUNT('Sheet1 (2)'[Ticket ID], 'Sheet1 (2)'[First response (hrs)] < 25)),COUNT('Sheet1 (2)'[Ticket ID]))
Solved! Go to Solution.
Hi,
Does this work?
=DIVIDE(CALCULATE(COUNT('Sheet1 (2)'[Ticket ID]),FILTER(Sheet1 (2), 'Sheet1 (2)'[First response (hrs)] < 25)),COUNT('Sheet1 (2)'[Ticket ID]))
Hi,
How can tht division yield the result as 6? The number of records with a response time as < 25 hours is 4. 4/10 should be 40%.
@Ashish_Mathur thank you for catching that, I edited the post. It should be 40%.
Hi,
Does this work?
=DIVIDE(CALCULATE(COUNT('Sheet1 (2)'[Ticket ID]),FILTER(Sheet1 (2), 'Sheet1 (2)'[First response (hrs)] < 25)),COUNT('Sheet1 (2)'[Ticket ID]))
Thank you sooooo much for this. Gahhh, I was struggling.
You are welcome.
Hi,
Just format the measure as a % age.
@Ashish_Mathur I thought of another use case for the formula. Is there a way we can filter out the blank values to make responses under 25 hours and over 0.01 hours? I tried to filter between 25 hours and 0, but it didn't filter the blank values out.
Here's the formula:
Tickets Responded (24hrs) = CALCULATE(COUNT('Week'[Ticket ID]), FILTER('Week', 'Week'[First response (hrs)] < 25 && 'Week'[First response (hrs)] >= 0.01))
Hi,
I am not clear. Share yoru dataset and show the expected result.
Are you able to download my .pbix file from the link?
Hi,
Just filter the measure int he visual filter section (look at the visualisation pane).
Hope this helps.
Is there a way I can just filter a measure shown on a card without the entire page?
My new link shows an example with other charts included: https://www.dropbox.com/s/3uoxzx93srmowmd/Example_help.pbix?dl=0
The error that you are getting, you need to end your first COUNT function with a ) just before your ",".
Thank you @Greg_Deckler
That fixed the formula error, but it didn't show the result I expected. When showing the result in a card, it showed 1. I was trying for the result to be 4.
Am I using the right formula for trying to divide tickets under 25hrs by the total?