Counting unique rows from a column after filtering another column

[Enigma]

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.

[mahoneypat · Accepted solution]

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

[Enigma]

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? 

[mahoneypat]

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/