Forum Discussion
Distinct count in calculated column
Hi all,
Absolute fresher here so please bear with me.
I am struggling to understand how to create a calculated column using distinct count - I've managed to find the solution but don't understand why it works.
Basically, I wanted to create a calculated column that shows 'unique vendors by product' using the below 'export orders' table
My initial attempt is as follows:
Unique Vendors by Product = CALCULATE(
DISTINCTCOUNT(export_orders[Vendor]),
export_orders[Product])
This gave me the error message 'cannot convert value 'product1' of type Text to True/False.
After some googling, I updated my function to:
Hi Anonymous ,
When you use calculate ,you need add filter condiction,and like the below（In the dax you started with, less expersion for filtering）:
CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])And if you want to make your first dax work work ,you need to adjust like the below:
Unique Vendors by Product3 = CALCULATE( DISTINCTCOUNT(export_orders[Vendor]),FILTER(ALL(export_orders), export_orders[Product]=EARLIER(export_orders[Product])))Refer the below output result:
And to learn more different between filter and allexcept ,refer the below article ,It is very specific in its presentation：
2 Replies
Anonymous
the second parameter of calculate is FILTER, which is (Optional) Boolean expressions or table expressions that defines filters, or filter modifier functions. e.g. column A=xxx
what you added was the column name. That's why you got the error meassage.
pls see the references below to learn calculate and allexcept
Hi Anonymous ,
When you use calculate ,you need add filter condiction,and like the below（In the dax you started with, less expersion for filtering）:
CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])And if you want to make your first dax work work ,you need to adjust like the below:
Unique Vendors by Product3 = CALCULATE( DISTINCTCOUNT(export_orders[Vendor]),FILTER(ALL(export_orders), export_orders[Product]=EARLIER(export_orders[Product])))Refer the below output result:
And to learn more different between filter and allexcept ,refer the below article ,It is very specific in its presentation：
