Forum Discussion
Helper IIICounting unique rows from a column after filtering another column
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.
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
3 Replies
- mahoneypat
Microsoft Employee
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
EnigmaThanks 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/