Forum Discussion
Count Rows In Table Default to 0 Returning to Many 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.
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.
