Count Based on Two Colums

[NPC]

Hello,

Help please! I have a table that looks like this

CustomerID	Event ID	Count of Events
12445	1001
12445	1002
12445	1003
12445	1003

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!

[NPC]

Hi @v-cgao-msft, it works with a test dataset. I guess it comes down to the size of my dataset.

[Prateek97 · Accepted solution]

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]

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. 

[v-cgao-msft]

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