Forum Discussion

Catman's avatar
Catman
Frequent Visitor
2 years ago
Solved

Count for ContractID

Hi, 

 

I am having trouble counting the number of occurences for an attribute, in this case ContractID & StartDate. Basically I need to count how many distinct Start Date for each ContractID and show the result in the same table.

 

Contract IDInvoice IDStart DateInvoice SalesCount
2024001AA1115/2/202410003
2024001AA1125/2/202510003
2024001AA1135/2/202610003
2024001AA1145/2/2026-10003
2024001AA1155/2/202610003
2024002AA1165/9/202410002
2024002AA1175/9/202510002
2024003AA1185/5/202410001
2024004AA1195/10/202410001
2024005AA1205/11/202410001
2024006AA1215/15/202410002
2024006AA1225/15/202510002

 

I have tried using the following: countrows(summarize('Table1', 'Table1'[ContractID]))

And the result is always 1 instead of my expected result (see below)

 

I think this is just a missing link to aggregate the count to the ContractID. Hope someone can help.

 

Thanks,

Cat

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    Hi Catman - Create a New Column as below:

     

    DistinctStartDateCount =
    CALCULATE(
        COUNTROWS(
            DISTINCT('countff'[Start Date])
        ),
        ALLEXCEPT('countff', 'countff'[Contract ID])
    )
     
    output FYR:
     

     

     

    It works, please check

     
    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi Catman - Create a New Column as below:

     

    DistinctStartDateCount =
    CALCULATE(
        COUNTROWS(
            DISTINCT('countff'[Start Date])
        ),
        ALLEXCEPT('countff', 'countff'[Contract ID])
    )
     
    output FYR:
     

     

     

    It works, please check

     
    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!
  • Catman's avatar
    Catman
    Frequent Visitor
    2 years ago

    Thank you so much, it is working as expected now.