topic Counting unique rows from a column after filtering another column in DAX Commands and Tips

Counting unique rows from a column after filtering another column

Enigma — Thu, 07 May 2020 18:58:40 GMT

Hi,

I have a measure that first filters out all rows from a date column that are older than a given date; and then, counts the rows in the 'Document Key' column.

Under1Year = COUNTX(FILTER('Inventory',[Content Current Date].[Date] >= CONVERT("03/01/2019",DATETIME)),'Inventory'[Document Key])

This was working fine.

Now, there's an additional requirement to count only unique values from the column. I modified the formula as below but none of them worked.

This one gave the same result as the above original formula(which I know is incorrect as I manualy checked there are duplicate values present in the 'Document Key' column after the date filter is applied)

Under1Year = COUNTX(FILTER('Inventory',[Content Current Date].[Date] >= CONVERT("03/01/2019",DATETIME)),DISTINCTCOUNT('Inventory'[Document Key]))

Then I tried this one and, as expected, it threw a runtime error as DISTINCTCOUNT only accepts a column as an argument.

Under1Year = DISTINCTCOUNT(COUNTX(FILTER('Inventory',[Content Current Date].[Date] >= CONVERT("03/01/2019",DATETIME)),'Inventory'[Document Key]))

Any ideas how to modify the formula so it only counts unique rows in the 'Document Key' column?

- Regards.

Re: Counting unique rows from a column after filtering another column

mahoneypat — Fri, 08 May 2020 01:32:44 GMT

How about this measure

NewMeasure = calculate(distinctcount(Inventory[Customer Key]), [Content Current Date].[Date] >= CONVERT("03/01/2019", DATETIME))

A couple questions though. Why are you converting to DateTime? Is your Date column in that format? Would >= Date(2019,3,1) work in its place?

And I would also suggest having a separate Date table and turn off the Auto date hierarchies (.[Date]).

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Re: Counting unique rows from a column after filtering another column

Enigma — Fri, 08 May 2020 11:18:17 GMT

Thanks mahoneypat

This worked perfectly!

And to answer your questions, I was converting the date as I was not aware how to use the DATE function. Now that I do, I am using that and not converting.

About a separate Date table - well, again, I don't have much idea about. Will do a bit of research. Any resources you can suggest that explains the pros and cons versus using date hierarchies?

Re: Counting unique rows from a column after filtering another column

mahoneypat — Fri, 08 May 2020 12:25:57 GMT

Glad it worked for you. Here are some useful links with more info.

https://www.youtube.com/watch?v=d4yAzhIQqNk

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/