# DAX and COUNTIFS Equivalent



## cognostom (Aug 27, 2015)

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_idtimestampscan_description77781410:22:26Success77781510:22:35Success77781610:22:43Success77781710:22:50Success77781810:23:02Success77897411:04:27Success77897511:04:32Success77969110:01:15Success77969210:01:17Success

<tbody>

</tbody><colgroup><col><col><col></colgroup>
Many thanks!


----------



## scottsen (Aug 29, 2015)

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 (Sep 1, 2015)

Thanks very much! That worked a treat


----------

