How to count all the non duplicated values

[Zaky]

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

[Zubair_Muhammad]

@Zaky 

As a MEASURE, try this

Measure =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( Table1[SRID] ),
            "Count", CALCULATE ( COUNT ( Table1[SRID] ) )
        ),
        [Count] = 1
    )
)

[Zaky]

Hi,

the expected table supposed to be like this..

[hnguy71 · Accepted 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.

[Zaky]

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

[hnguy71]

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())

[Zaky]

@hnguy71 

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

[hnguy71]

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?

[Zaky]

@hnguy71 

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

[hnguy71]

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")

[Zaky]

@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.

[Zaky]

Hi @hnguy71 ,

Amazing work! The solutions is works as per required.

Thanks dude.

[hnguy71]

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