Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Solved! Go to 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:
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:
@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
Proud to be a Super User!