cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper IV
Helper IV

How to count all the non duplicated values

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 DateSRID
08/02/19REQ000001629034
08/02/19REQ000001629185
08/02/19REQ000001629874
08/02/19REQ000001630004
08/02/19REQ000001630005
08/02/19REQ000001630201
08/02/19REQ000001590017
08/02/19REQ000001590018
08/02/19REQ000001590019
08/02/19REQ000001590022
15/02/19REQ000001629034
15/02/19REQ000001629185
15/02/19REQ000001629874
15/02/19REQ000001630004
15/02/19REQ000001630005
15/02/19REQ000001625470
15/02/19REQ000001602982
15/02/19REQ000001603859
15/02/19REQ000001605304
1 ACCEPTED SOLUTION
Responsive Resident
Responsive Resident

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.

 

ticket_system.PNG

View solution in original post

12 REPLIES 12
Community Champion
Community Champion

@Zaky 

 

As a MEASURE, try this

 

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


Hi,

 

the expected table supposed to be like this..

 

table1.PNG

Responsive Resident
Responsive Resident

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.

 

ticket_system.PNG

View solution in original post

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

 

ageing range.PNG

 

 

Can u help me on this?

 

Thanks

 

 

Responsive Resident
Responsive Resident

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

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

 

exc.PNG

 

Could you please advise..

 

Thanks again

Responsive Resident
Responsive Resident

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?

@hnguy71 

 

For testing, this is the dataset that i have.

 

Ageing DateAgeing RangeSRIDSummaryProduct Categorization Tier 3WO Assignee Group
01/03/198 - 14 DaysREQ000001662552Request for Multi Functional PrinterLeasingFIRST PRINT AND SCAN SERVICES
01/03/198 - 14 DaysREQ000001653740Request for Docking StationDocking StationFIRST DAAS
01/03/198 - 14 DaysREQ000001653742Request for Docking StationDocking StationFIRST DAAS
01/03/198 - 14 DaysREQ000001654392Request for MonitorMonitorFIRST DAAS
01/03/198 - 14 DaysREQ000001654394Request for Riser SetRiser SetFIRST DAAS
01/03/193 - 7 DaysREQ000001663209Request for MonitorMonitorFIRST DAAS
01/03/193 - 7 DaysREQ000001663775Request for Docking StationDocking StationFIRST DAAS
01/03/193 - 7 DaysREQ000001664486Request for Docking StationDocking StationFIRST DAAS
01/03/193 - 7 DaysREQ000001664546Request for Docking StationDocking StationFIRST DAAS
01/03/193 - 7 DaysREQ000001664547Request for MonitorMonitorFIRST DAAS
01/03/193 - 7 DaysREQ000001664792Request for MonitorMonitorFIRST DAAS
07/03/19> 90 DaysREQ000001435665Request for Workstation (Mobile)Mobile WSFIRST DAAS
07/03/19> 90 DaysREQ000001445920Request for Docking StationDocking StationFIRST DAAS
07/03/19> 90 DaysREQ000001413344Request for Workstation (Mobile)Mobile WSFIRST DAAS
07/03/19> 90 DaysREQ000001377752Request for Workstation (Mobile)Mobile WSFIRST DAAS
07/03/19> 90 DaysREQ000001378645Request for Workstation (Mobile)Mobile WSFIRST DAAS
Responsive Resident
Responsive Resident

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.

 

aging_range.PNGaging_range_2.PNG

 

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

range1.PNG

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.Smiley Sad

 

 

 

 

 

Hi @hnguy71 ,

 

Amazing work! The solutions is works as per required.

 

 

Thanks dude.

 

Responsive Resident
Responsive Resident

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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors