Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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?
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/
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
23 | |
10 | |
10 | |
9 | |
7 |