Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi folks,
This is such an important topic for me, so help a girl out, please.
So, I have, say, 2 support cases, which have gone through different statuses in different dates.
Case | Status | From | Until | Days |
A | 1-New | 1/1/18 0:00 | 1/15/18 0:00 | 15 |
A | 2-In Processing | 1/15/18 0:01 | 1/17/18 0:00 | 2 |
B | 1-New | 1/7/18 0:00 | 1/14/18 0:00 | 7 |
C | 2-In Processing | 1/14/18 0:01 | 1/20/18 0:00 | 6 |
I'd like to see how many Cases where in each Status at a given date (and hour, given the business).
In the example above, on 1/2/18 0:00 we only have one Case: A, in 1-New, and none in 2-Processing.
On 1/17/18 0:00 we have both cases in 2-Processing, and none in 1-New.
Basically, I need the user to filter one dane and the report to give this simple result.
The problem is, I have no idea how to do that.
Major thanks in advance, people.
Alice
Solved! Go to Solution.
you need separate Calendar table that would have all the dates
if you don't have one you can create it with this sytnax (adjust the dates properly)
Calendar = CALENDAR(DATE(2018,1,1),date(2018,12,31))
this table it should have NO active joins to you data table
then this syntax should work (in the visual use date from Calendar table)
Measure = VAR ShownDate = MAX('Calendar'[Date]) VAR RelevantRows = FILTER('Table','Table'[From]<=ShownDate+1 && 'Table'[Until]>=ShownDate) RETURN COUNTROWS(RelevantRows)
so this is the sytnax I have with adjusted names:
Measure = VAR ShownDate = MAX('Calendar'[Date]) VAR RelevantRows = FILTER('Table','Table'[Change Date]<ShownDate+1 && 'Table'[Change Valid Until]>=ShownDate) RETURN COUNTROWS(RelevantRows)
I cannot see what is working wrong here - can you specify which Enhancement Number has the data that shows the issue?
you need separate Calendar table that would have all the dates
if you don't have one you can create it with this sytnax (adjust the dates properly)
Calendar = CALENDAR(DATE(2018,1,1),date(2018,12,31))
this table it should have NO active joins to you data table
then this syntax should work (in the visual use date from Calendar table)
Measure = VAR ShownDate = MAX('Calendar'[Date]) VAR RelevantRows = FILTER('Table','Table'[From]<=ShownDate+1 && 'Table'[Until]>=ShownDate) RETURN COUNTROWS(RelevantRows)
It partially works. Namely, it only displays the Cases for which changes were made IN that respective day 😞
For the rest, it's zero.
can you share more accurate data sample then?
Hurray!
Enhancement Number | Last New Value | Change Date | Change Valid Until |
801 | 4 - Acknowledged | 14/09/2018 10:38 | 23/10/2018 0:00 |
859 | 4 - Acknowledged | 07/09/2018 14:42 | 23/10/2018 0:00 |
860 | 4 - Acknowledged | 07/09/2018 14:22 | 23/10/2018 0:00 |
875 | 4 - Acknowledged | 14/09/2018 10:19 | 23/10/2018 0:00 |
920 | 1 - New | 03/09/2018 9:31 | 09/10/2018 9:49 |
932 | 1 - New | 11/09/2018 13:19 | 19/10/2018 14:42 |
976 | 1 - New | 28/09/2018 14:18 | 08/10/2018 7:35 |
976 | 2 - For Review | 08/10/2018 7:35 | 09/10/2018 9:54 |
984 | 1 - New | 03/10/2018 13:18 | 03/10/2018 13:18 |
984 | 2 - For Review | 03/10/2018 13:18 | 19/10/2018 14:05 |
985 | 1 - New | 03/10/2018 15:03 | 19/10/2018 14:03 |
986 | 1 - New | 03/10/2018 15:14 | 11/10/2018 8:43 |
5 | 2 - For Review | 18/10/2018 14:49 | 19/10/2018 13:55 |
16 | 1 - New | 19/10/2018 12:27 | 23/10/2018 0:00 |
so this is the sytnax I have with adjusted names:
Measure = VAR ShownDate = MAX('Calendar'[Date]) VAR RelevantRows = FILTER('Table','Table'[Change Date]<ShownDate+1 && 'Table'[Change Valid Until]>=ShownDate) RETURN COUNTROWS(RelevantRows)
I cannot see what is working wrong here - can you specify which Enhancement Number has the data that shows the issue?
Now it's working - my bad, I must have changed something in your initial variable.
Thank YOU!!!!