Count unique values based on condition

[Anonymous]

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

[v-yingjl · Accepted solution]

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.

[BeaBF]

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

Thx,

B.

[Anonymous]

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

[BeaBF]

@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!

B.

[Anonymous]

@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.

[Anonymous]

@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

[Anonymous]

@Fowmy @amitchandak @VahidDM @Greg_Deckler