Count Rows In Table Default to 0 Returning to Many Results

[jharsh]

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.

[v-yanjiang-msft · Accepted solution]

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 _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

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.

[jharsh]

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.

[johnt75]

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

[jharsh]

Thanks for the feedback.  Unfortunately, that formula still returns all results from the Customer table instead of the filtered by Manager results.