topic Re: DAX Distinct Count with Filter in DAX Commands and Tips

DAX Distinct Count with Filter

Anonymous — Mon, 25 May 2020 02:20:39 GMT

I'm comparing 2 columns; [Ticket Number] and [Scan datetime] in the same table.

I need to find the total number of unique tickets with a scan. I came up with the following formula but not sure if its correct....

TotalAccepScan:=CALCULATE(DISTINCTCOUNT(Table_name[TICKET]),Table_name[ACCEPTANCE_DATETIME]<>BLANK())

Re: DAX Distinct Count with Filter

Greg_Deckler — Mon, 25 May 2020 03:08:13 GMT

That's a very difficult question to answer with the information provided Anonymous . Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: DAX Distinct Count with Filter

Anonymous — Mon, 25 May 2020 03:34:14 GMT

Greg_Deckler my apologies. Here is sample dataset.

Output result: What is the count of (unique) tickets that have a scan date? My current formula is:

Total_Tickets_With_Scan:=CALCULATE(DISTINCTCOUNT(Table_Name[TICKET]),Table_Name[SCAN_DATETIME]<>BLANK())

Hope that made sense.

TICKET	SCAN_DATETIME
FF166549317	28/04/2020 12:52
FF166549626
FF166549820
FF166550071
FF166550329	28/04/2020 12:51
FF166550394
FF166550403
FF166550448	28/04/2020 16:53
FF166551125	28/04/2020 17:53
FF166551545

Re: DAX Distinct Count with Filter

Greg_Deckler — Mon, 25 May 2020 03:38:55 GMT

Anonymous - I do not see anything wrong with that calculation. It is probably not how I would have structured it but because I try to avoid using CALCULATE but there is nothing wrong with it if your intention is to get a distinct count of only the TICKET's that have a SCAN_DATETIME.

Re: DAX Distinct Count with Filter

Anonymous — Mon, 25 May 2020 03:57:50 GMT

Greg_Deckler thank you for confirming. Appreciate your help.