Forum Discussion

MasterSonic's avatar
MasterSonic
Icon for Helper IV rankHelper IV
3 years ago
Solved

Count unique per other columns

Hello,

CodePlanStatus
101BuyReady
102SellReady
102SellReady


I would like to count unique code by Status and Planm but I have duplicates in Code column.

So on Report,  I could drop Plan and "Count of Status" showing 1 for Plan SELL, based on table above.



I have this measurment but it doesn't do the job, as is showing 2, counting Code per plan ONLY, without looking at Status

Measur1 = CALCULATE(DISTINCTCOUNT('Table'[Code]),ALLEXCEPT('Table'[Plan]))

Is there any way to perform calculaction please?
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    not sure if i get you, try this

    Measure2 = 

    COUNTROWS(
        SUMMARIZE(
            TableName, 
            TableName[Plan], 
            TableName[Code]
        )
    )

4 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi   MasterSonic 

    try like:
     
    Measure2 = 
    COUNTROWS(
        SUMMARIZE(
            TableName, 
            TableName[Plan], 
            TableName[Status]
        )
    )
    • MasterSonic's avatar
      MasterSonic
      Icon for Helper IV rankHelper IV
      3 years ago

      Hi,
      thanks but It does not give the wanted result.

      lets extend my table

      CodePlanStatus
      101BuyReady
      102SellReady
      102SellReady
      103SellReady


      Now it should showing  col=Plan value=Sell  should be 2
      As we have code duplicated.

      I would like to count Code per Plan where value in column Status is Ready, but do not count duplicated Codes. Maybe this will help

      • FreemanZ's avatar
        FreemanZ
        Icon for Super User rankSuper User
        3 years ago

        not sure if i get you, try this

        Measure2 = 

        COUNTROWS(
            SUMMARIZE(
                TableName, 
                TableName[Plan], 
                TableName[Code]
            )
        )