Distinct count in calculated column

[Anonymous]

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: 

Unique Vendors by Product = CALCULATE(

DISTINCTCOUNT(export_orders[Vendor]),

allexcept(export_orders,export_orders[Product]))

This gives me the desired calculated column, however, I am confused as to why my initial function did not work - why is 'allexcept' necessary to pull the desired result?

[v-luwang-msft · Accepted solution]

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：

Filter and Allexcept

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[ryan_mayu]

@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

ALLEXCEPT function (DAX) - DAX | Microsoft Docs

CALCULATE function (DAX) - DAX | Microsoft Docs