Forum Discussion
Count Rows In Table Default to 0 Returning to Many Results
I am trying to count the number of rows in a left join table defaulting to zero if there are none. When I do this the other table filters are no longer applied and I get all the records in the count table back.
Example
Managers Offices SalesPeople Customers
____________________________________________________________________________________________________
Manager Manager Office SalesPerson Office SalesPerson Customer
Bob Bob 1 Tom 1 Tom Joe
Matt 1----* Matt 2 1-----* Harry 1 1---* Harry Jane
Frank 1 Harry George Greg 2
Expected Results when Manager is selected from slicer
SalesPerson Customer Count
Tom 1
Harry 2
Frank 0
Actual Results
SalesPerson Customer Count
Tom 1
Harry 2
Frank 0
Greg 0
I am doing a measure from the Manager table
CustomerCount = IF(ISBLANK(COUNT(Customers[Customer])),0,COUNT(Customers[Customer])) but this is giving me all sales person not just the ones in the office for the manager selected.
Hi jharsh ,
I modify the formula, and it works fine now.
Show Sales Person in Table = var selectedManager = SELECTEDVALUE(Managers[Manager]) var currentOffice = SELECTEDVALUE(SalesPeople[Office]) var employeesManager = LOOKUPVALUE( Offices[Manager], Offices[Office], currentOffice ) return IF( employeesManager = selectedManager||NOT(ISFILTERED('Managers'[Manager])), 1 )I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
I think the below should work
CustomerCount = COALESCE( CALCULATE( COUNTROWS( Customers ) ), 0 )
The CALCULATE introduces context transition so should pick up the filter on manager
Thanks for the feedback. Unfortunately, that formula still returns all results from the Customer table instead of the filtered by Manager results.
The issue is the COALESCE. By returning 0 instead of BLANK each row has a value, and so each row is returned. But removing the COALESCE and returning BLANK instead means that not all employees are returned, only the ones with at least one customer, so Greg doesn't show up.
You can get around this by removing the COALESCE from the measure so that it returns BLANK instead of 0, then creating a new measure
Show Sales Person in Table =
var selectedManager = SELECTEDVALUE(Managers[Manager])
var currentOffice = SELECTEDVALUE(SalesPeople[Office])
var employeesManager = LOOKUPVALUE( Offices[Manager], Offices[Office], currentOffice )
return IF( employeesManager = selectedManager, 1 )Then making some tweaks to your visuals. Firstly, on the visual showing the sales person and the count of customers, make sure that you're using the SalesPeople[SalesPerson] column and not the Customers[SalesPerson]. Then choose the SalesPerson field in the Values well, click the down arrow and choose "Show items with no data".
Next add the new measure you created as a filter on this visual, only show items when value is 1.
That should give you what you're after.
This works for only showing the SalesPerson records for the manager. I can add the count of Customers without getting the unwanted customers as well. The only issue is the count is empty instead of 0 if the SalesPerson has no customers.
This also doesn't work when no Manager is selected in the slicer to see all results.
Hi jharsh ,
I modify the formula, and it works fine now.
Show Sales Person in Table = var selectedManager = SELECTEDVALUE(Managers[Manager]) var currentOffice = SELECTEDVALUE(SalesPeople[Office]) var employeesManager = LOOKUPVALUE( Offices[Manager], Offices[Office], currentOffice ) return IF( employeesManager = selectedManager||NOT(ISFILTERED('Managers'[Manager])), 1 )I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
