Forum Discussion

NPC's avatar
NPC
Helper I
2 years ago
Solved

Count Based on Two Colums

Hello,

 

Help please! I have a table that looks like this

 

CustomerIDEvent IDCount of Events
124451001 
124451002 
124451003 
124451003 

 

I need to fill the "Count Of Events" column in this case with 3 for this customer ID because there were three unique events. The last one is a duplicate and I don't want to count it. 

 

I was using this formular until I realized that it  counts these as 4 Events, not 3. 

 

OutageCount =
CALCULATE(
    COUNTROWS(Outages),
    FILTER(Outages, Outages[CustomerID] = EARLIER(Outages[CustomerID]))
)

 

Any suggestions on how I can modfiy the formular above? A completly different formular is also welcome.

 

Thank you!

Need Help
  • Prateek97's avatar
    Prateek97
    2 years ago

    Hi NPC ,

     

    You can simply do Disttinctcount here instead of COUNTROWS.

     

    OutageCount =
    CALCULATE(
        DISTINCTCOUNT(Outages[EventID]),
        FILTER(Outages, Outages[CustomerID] = EARLIER(Outages[CustomerID]))
    )

     

    Please mark as solution, if it works.

4 Replies

  • Prateek97's avatar
    Prateek97
    Resolver III
    2 years ago

    Hi NPC ,

     

    You can simply do Disttinctcount here instead of COUNTROWS.

     

    OutageCount =
    CALCULATE(
        DISTINCTCOUNT(Outages[EventID]),
        FILTER(Outages, Outages[CustomerID] = EARLIER(Outages[CustomerID]))
    )

     

    Please mark as solution, if it works.

    • NPC's avatar
      NPC
      Helper I
      2 years ago

      Thank you Prateek97. For some reason I'm getting the "Working on it" message when I try to add the column and I get stuck there forever. Not sure if its because the table is very big. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi NPC ,

        It's a very simple calculation that shouldn't consume too many resources.🤔

        Please try:

        1. Update Power BI: Ensure you're using the latest version of Power BI Desktop.
        Download Power BI Desktop from Official Microsoft Download Center

        2. Check for large model: If you're working with a large model, it might take longer to process. Consider optimizing your data model or filtering the data before adding the column.

        Best Regards,
        Gao
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

        How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

  • NPC's avatar
    NPC
    Helper I
    2 years ago

    Hi Anonymous, it works with a test dataset. I guess it comes down to the size of my dataset.