DAX and COUNTIFS Equivalent

[cognostom]

Hi,
This is my first post on this site although I use it most days to find solutions. I'm also fairly new to PowerPivot.
I am analysing data from our admissions system. Two of the columns I am looking at contain ticket_id and scan_description. The theory is that no single ticket can be successfully scanned more than once, meaning that each ticket_id should be unique in this column. However, because of a bug in the system this is currently not the case. In Excel i simply use COUNTIFS to tell me that if the scan was successful, does the ticket_id exist more than once in that column. Now that i have moved my data over to PowerPivot, i would like to perform the same function using DAX but cannot get it to work. I have been using a combination of CALCULATE, COUNT AND FILTER but have run out of ideas. Here's a sample of my data:

ticket_id	timestamp	scan_description
777814	10:22:26	Success
777815	10:22:35	Success
777816	10:22:43	Success
777817	10:22:50	Success
777818	10:23:02	Success
778974	11:04:27	Success
778975	11:04:32	Success
779691	10:01:15	Success
779692	10:01:17	Success

<tbody>
</tbody><colgroup><col><col><col></colgroup>

Many thanks!

 

[scottsen]

You could create a calculated column that contains the count of same id tix.
=CALCULATE(COUNTROWS(Tickets), FILTER(Tickets, Tickets[Ticket_Id] = EARLIER(Tickets[Ticket_Id]))

Then use that column for various fancy things.

 

[cognostom]

Thanks very much! That worked a treat