Forum Discussion
Advocate IVCounting 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.
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 ])) )
6 Replies
- AllisonKennedy
Community Champion
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 ]))
BelieverThanks 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."- AllisonKennedy
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 ])) )