Forum Discussion
Advocate IVCounting subsets of rows
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 ])) )
Community ChampionIf performance is important you should do this as a Measure, not a column.
Try this:
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 ])) )
- Believer
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.
- AllisonKennedy
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.