topic Re: Count unique values based on condition in DAX Commands and Tips

Count unique values based on condition

Anonymous — Tue, 19 Oct 2021 09:30:32 GMT

Hi Guys,

I have some order and production line data. What I want to do is for each production line I want to count the number of occurance of that production line in the table only if there is one order number relating to that production line.

This is the sample data and below is the required answer.

So I want to create a table by Production line number with no of Orders.

Please help.

Link to PBI file
https://drive.google.com/file/d/1syX7jSgZHhEuhDYvBuIqAiLSm52GUR9u/view?usp=sharing

Re: Count unique values based on condition

Anonymous — Tue, 19 Oct 2021 10:10:49 GMT

Fowmy amitchandak VahidDM Greg_Deckler

Re: Count unique values based on condition

BeaBF — Tue, 19 Oct 2021 10:31:27 GMT

Anonymous Hi! I can't open the file, Can you paste here the data table on which to calculate what you requested?

Thx,

Re: Count unique values based on condition

Anonymous — Tue, 19 Oct 2021 10:41:58 GMT

BeaBF

here is the Data

Order No.Production Line No.

Order1	A
Order1	B
Order1	C
Order2	A
Order3	A
Order4	C
Order4	D
Order4	E
Order5	A
Order6	C
Order7	D
Order8	E
Order8	D

Re: Count unique values based on condition

Anonymous — Tue, 19 Oct 2021 10:43:49 GMT

Order No.	Production Line No.
Order1	A
Order1	B
Order1	C
Order2	A
Order3	A
Order4	C
Order4	D
Order4	E
Order5	A
Order6	C
Order7	D
Order8	E
Order8	D

Re: Count unique values based on condition

BeaBF — Tue, 19 Oct 2021 12:49:32 GMT

Anonymous Here two calculated columns to obtain your result:

COUNTT = CALCULATE(COUNT(Tabella[Order No.]), ALLEXCEPT(Tabella,Tabella[Order No.]))

TOTAL_ORDERS = IF(CALCULATE(SUM(Tabella[COUNTT]), Tabella[COUNTT] = 1) = BLANK(), 0, CALCULATE(SUM(Tabella[COUNTT]), Tabella[COUNTT] = 1))

Hope that's what you wanted!

Re: Count unique values based on condition

Anonymous — Wed, 20 Oct 2021 04:19:45 GMT

BeaBF Thank you for the response. But we can not do it in a calculated column as the result should dynamically change based on the filters applied.

Re: Count unique values based on condition

v-yingjl — Fri, 22 Oct 2021 07:00:07 GMT

Hi Anonymous ,

You can use a slicer for the Order.No, create a calculated table:

Table = DISTINCT('Data'[Production Line No.])

A measure to count:

Count = CALCULATE ( COUNT ( Data[Order No.] ), FILTER ( ALLSELECTED ( Data ), 'Data'[Production Line No.] IN DISTINCT ( 'Table'[Production Line No.] ) ) ) + 0

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.