Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have this table where it has different ageing date (08/02/2019 and 15/02/2019).
What I want to is to get the count of remaining ticket of [SRID}.
So from this table i should have only 9 SRID tickets still open. Can you help with the measure that i can use?
Hope this make sense.
Thank you for the help.
Ageing Date | SRID |
08/02/19 | REQ000001629034 |
08/02/19 | REQ000001629185 |
08/02/19 | REQ000001629874 |
08/02/19 | REQ000001630004 |
08/02/19 | REQ000001630005 |
08/02/19 | REQ000001630201 |
08/02/19 | REQ000001590017 |
08/02/19 | REQ000001590018 |
08/02/19 | REQ000001590019 |
08/02/19 | REQ000001590022 |
15/02/19 | REQ000001629034 |
15/02/19 | REQ000001629185 |
15/02/19 | REQ000001629874 |
15/02/19 | REQ000001630004 |
15/02/19 | REQ000001630005 |
15/02/19 | REQ000001625470 |
15/02/19 | REQ000001602982 |
15/02/19 | REQ000001603859 |
15/02/19 | REQ000001605304 |
Solved! Go to Solution.
Sticking to my calculated columns:
Closed_Tickets = var FindOtherDate = CALCULATE(MAX(Sheet1[Ageing Date]), FILTER(Sheet1, Sheet1[SRID]=EARLIER(Sheet1[SRID]))) return IF(Sheet1[Ageing Date] <> FindOtherDate && Sheet1[CountForDupes] >= 2, "Closed")
Remaining_Tickets = var MaxDate = MAX(Sheet1[Ageing Date]) return IF(Sheet1[Ageing Date] < MaxDate && Sheet1[CountForDupes] = 1, "Remaining")
New_Tickets = var MaxDate = MAX(Sheet1[Ageing Date]) return IF(Sheet1[Ageing Date] = MaxDate && Sheet1[CountForDupes] = 1, "New")
From there, you do a count for the above mentioned.
As a MEASURE, try this
Measure = COUNTROWS ( FILTER ( ADDCOLUMNS ( VALUES ( Table1[SRID] ), "Count", CALCULATE ( COUNT ( Table1[SRID] ) ) ), [Count] = 1 ) )
Hi,
the expected table supposed to be like this..
Sticking to my calculated columns:
Closed_Tickets = var FindOtherDate = CALCULATE(MAX(Sheet1[Ageing Date]), FILTER(Sheet1, Sheet1[SRID]=EARLIER(Sheet1[SRID]))) return IF(Sheet1[Ageing Date] <> FindOtherDate && Sheet1[CountForDupes] >= 2, "Closed")
Remaining_Tickets = var MaxDate = MAX(Sheet1[Ageing Date]) return IF(Sheet1[Ageing Date] < MaxDate && Sheet1[CountForDupes] = 1, "Remaining")
New_Tickets = var MaxDate = MAX(Sheet1[Ageing Date]) return IF(Sheet1[Ageing Date] = MaxDate && Sheet1[CountForDupes] = 1, "New")
From there, you do a count for the above mentioned.
Hi @hnguy71 ,
From there i want to create a new table to count the Ageing Range for the total of Remaining Ticket.
Ageing Range for PC/NB Acquisition and delivery date 08/02/2019 supposed to count 150 ticket instead of 159, on which i used the calculated column syntax as below
8-14 Days = CALCULATE(COUNTROWS('WO Raw Data for Ageing New'),'WO Raw Data for Ageing New'[Ageing Range]="8 - 14 Days")
Can u help me on this?
Thanks
Have you tried adding an additional filter for remaining tickets?
8-14 Days = CALCULATE(COUNTROWS('WO Raw Data for Ageing New'),'WO Raw Data for Ageing New'[Ageing Range]="8 - 14 Days", 'WO Raw Data for Ageing New'[Remaning_Tickets] <> BLANK())
Noted that the formula for the remaining ticket for ageing range "8 - 14 days" is counts if using the following:
#8-14 Remaining_Tickets =
var FindOtherDate = CALCULATE(COUNTROWS('WO Raw Data for Ageing'),'WO Raw Data for Ageing'[Ageing Range] ="8 - 14 Days", 'WO Raw Data for Ageing'[*Remaining_Tickets] <> BLANK())
return
IF('WO Raw Data for Ageing'[Ageing Date]<> FindOtherDate && 'WO Raw Data for Ageing'[*CountForDupes]>= 1, "Remaining")
How can i count for the rest of ageing ranges "3 -7", "15 - 30", ">90 Days"?
Should i create separate ? However when i tried create new column example for range "3 -7 days", there is exclamation notes as follow:
Could you please advise..
Thanks again
Hi Zaky,
The formula you have there shouldn't work since there's a circular reference. In addition, your #8 -14 Remaning Ticket calculated column shouldn't work. Do you have an additional column somewhere that displays aging range? Rather, is there additional columns that you have in your dataset that you did not previously provide?
For testing, this is the dataset that i have.
Ageing Date | Ageing Range | SRID | Summary | Product Categorization Tier 3 | WO Assignee Group |
01/03/19 | 8 - 14 Days | REQ000001662552 | Request for Multi Functional Printer | Leasing | FIRST PRINT AND SCAN SERVICES |
01/03/19 | 8 - 14 Days | REQ000001653740 | Request for Docking Station | Docking Station | FIRST DAAS |
01/03/19 | 8 - 14 Days | REQ000001653742 | Request for Docking Station | Docking Station | FIRST DAAS |
01/03/19 | 8 - 14 Days | REQ000001654392 | Request for Monitor | Monitor | FIRST DAAS |
01/03/19 | 8 - 14 Days | REQ000001654394 | Request for Riser Set | Riser Set | FIRST DAAS |
01/03/19 | 3 - 7 Days | REQ000001663209 | Request for Monitor | Monitor | FIRST DAAS |
01/03/19 | 3 - 7 Days | REQ000001663775 | Request for Docking Station | Docking Station | FIRST DAAS |
01/03/19 | 3 - 7 Days | REQ000001664486 | Request for Docking Station | Docking Station | FIRST DAAS |
01/03/19 | 3 - 7 Days | REQ000001664546 | Request for Docking Station | Docking Station | FIRST DAAS |
01/03/19 | 3 - 7 Days | REQ000001664547 | Request for Monitor | Monitor | FIRST DAAS |
01/03/19 | 3 - 7 Days | REQ000001664792 | Request for Monitor | Monitor | FIRST DAAS |
07/03/19 | > 90 Days | REQ000001435665 | Request for Workstation (Mobile) | Mobile WS | FIRST DAAS |
07/03/19 | > 90 Days | REQ000001445920 | Request for Docking Station | Docking Station | FIRST DAAS |
07/03/19 | > 90 Days | REQ000001413344 | Request for Workstation (Mobile) | Mobile WS | FIRST DAAS |
07/03/19 | > 90 Days | REQ000001377752 | Request for Workstation (Mobile) | Mobile WS | FIRST DAAS |
07/03/19 | > 90 Days | REQ000001378645 | Request for Workstation (Mobile) | Mobile WS | FIRST DAAS |
hmmm...
looks like you already have all the data you need to figure out your aging range without the need for a calculated column / measure.
If you need a specific measure for the count of remaining tickets for the aging ranges:
05_Remaining_3_to_7_Days = CALCULATE([03_Remaining_Tickets], Sheet1[Ageing Range] = "3 - 7 Days") 05_Remaining_8_to_14_Days = CALCULATE([03_Remaining_Tickets], Sheet1[Ageing Range] = "8 - 14 Days") 05_Remaining_Above90 = CALCULATE([03_Remaining_Tickets], Sheet1[Ageing Range] = ">90 Days")
@hnguy71 ,
I have created a specific formula for ageing range "8 - 14 days" 8-14 Days = CALCULATE(COUNTROWS('WO Raw Data For Ageing'),'WO Raw Data For Ageing'[Ageing Range]="8 - 14 Days",'WO Raw Data For Ageing'[*Remaining_Tickets] <> BLANK()), but the result only counts for the date 07/03/2019 taking into calculation for all duplicates tickets. The result is not as expected because i think the table [*Remaining_Tickets] is count the duplicates tickets as 1 ticket. for date 07/03/2019 and 15/03/2019 *Remaining_Tickets = var FindOtherDate = CALCULATE(MAX('WO Raw Data For Ageing'[Report Date]), FILTER('WO Raw Data for Ageing', 'WO Raw Data For Ageing'[Work Order ID]=EARLIER ('WO Raw Data For Ageing'[Work Order ID])))
return
IF('WO Raw Data For Ageing'[Report Date]<> FindOtherDate && 'WO Raw Data for Ageing'[*CountForDupes]>= 2, "Remaining")
New column formula for ageing range "8-14 days" that i created as follow:
8-14 Days = CALCULATE(COUNTROWS('WO Raw Data For Ageing'),'WO Raw Data For Ageing'[Ageing Range]="8 - 14 Days",'WO Raw Data For Ageing'[*Remaining_Tickets] <> BLANK())
what i want is to calculate for the remaining ticket by date 07/03/2019 and 15/03/2019. Perhaps i should created a new column for the [*Remaining_Tickets] to get the right counts of remaining tickets for both dates?
Pls help. I'm stuck.
One way of doing it would be to create a calculated column:
CountForDupes = var FindDupes = Sheet1[SRID] return CALCULATE(COUNTROWS(Sheet1), ALL(Sheet1), FindDupes=Sheet1[SRID])
Anything equal to 1 should be a unique / non-duplicated ID
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |