Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count unique values based on condition

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's avatar
    v-yingjl
    4 years ago

    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.

     

7 Replies

  • BeaBF's avatar
    BeaBF
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      BeaBF 

      here is the Data

       

      Order No.Production Line No.

      Order1A
      Order1B
      Order1C
      Order2A
      Order3A
      Order4C
      Order4D
      Order4E
      Order5A
      Order6C
      Order7D
      Order8E
      Order8D
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago
      Order No.Production Line No.
      Order1A
      Order1B
      Order1C
      Order2A
      Order3A
      Order4C
      Order4D
      Order4E
      Order5A
      Order6C
      Order7D
      Order8E
      Order8D
      • BeaBF's avatar
        BeaBF
        Super User
        4 years ago

        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.
  • v-yingjl's avatar
    v-yingjl
    Community Support
    4 years ago

    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.