cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## Counting subsets of rows

Considering the following sample data, where the first 4 columns are in a table and the last column (Cust_RecID_Count) is calculated:

 Customer RecID SecID PosID Cust_RecID_Count A 1 12 1 2 A 1 15 1 2 A 2 12 1 1 B 2 17 1 3 B 2 19 2 3 B 2 55 1 3 B 3 19 1 1 C 1 52 1 1

How would I calculate Cust_RecID_Count, the row count for each Customer/RecID pair?  Any filters which may be applied in the report page should not affect the value.  Every time Customer/RecID A/1 appears, the result should always be 2.  For B/2 it would be 3, and all others are 1.

I’ve been using Power BI for a while but have not done much with CALCULATE, been tearing my hair out trying to get it right.  Beginning to wonder whether a CALCULATE expression is even the way to go vs a completely different method, such as dynamically building a separate table of Customer/RecID/Count?  Performance Is fairly important since my source table has over 12 million records.

1 ACCEPTED SOLUTION
Super User

You can filter the measure instead of the visual:

Cust_RecID_Count = IF(ISBLANK([Customer Count]), BLANK(), CALCULATE([Customer Count], ALLEXCEPT('Table', 'Table'[Customer  ], 'Table'[RecID  ])) )

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

6 REPLIES 6
Super User

If performance is important you should do this as a Measure, not a column.

Try this:

Cust_RecID_Count = CALCULATE([Customer Count], ALLEXCEPT('Table', 'Table'[Customer  ], 'Table'[RecID  ]))

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Thanks for the solution. The good news is it works perfectly based on what I told you, and I was able to get it working on my production data. The bad news is I discovered that there’s another table and relationship involved, and when I add that in it makes a mess of the results, adding in a lot of rows which shouldn’t be there. Although I can filter the visual to remove the blank “Customer Count” that strikes me as a hack.
I added the Customers table to the model, relationship, etc. Here are the screen shots, not sure why I cannot upload the updated PBIX file here... I get an error "The file type (.pbix) is not supported."

Super User

@Believer  Re uploading .pbix files - you need to be a superuser to have that permission, so you can upload to OneDrive and share the link.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Super User

You can filter the measure instead of the visual:

Cust_RecID_Count = IF(ISBLANK([Customer Count]), BLANK(), CALCULATE([Customer Count], ALLEXCEPT('Table', 'Table'[Customer  ], 'Table'[RecID  ])) )

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

If I remove "Customer" (abbreviation) from the table, the counts are incorrect.  I understand this is because I removed "Customer" from the row context, so now I am seeing count of each RecID period.  How do I get Customer back into the row context without having it displayed?  (Given the relationship between the tables, I thought the context would have been impled.)  I could leave the column in and set the width to zero, but again that feels like a hack...

Thank you very much, such a simple fix!  Pretty sure this is also the answer to another question I've had open for a while with no answers...

I thought there may have been something more extravagant at work here.  I'm still trying to absorb the nuance of CALCULATE filters.  I recently saw that calculate filters are always tables (thanks SQLBI!) and thought somehow this was multiplying the table siginifcantly (given all the extra rows appearing) and that I was using the incorrect approach.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors