Count of active customers alongside a count of all customers

[QuentinB]

This sounds ridiculously simple, but I can't seem to ask the right question of the search to find the answer.

I have a list of sales, each sale being to a single customer at a single branch

I have a table of customers, each customer belongs to a single branch

What I want is  a table of Branch, Purchasing Customers, Total Branch Customers

Because of the relationship from Sales to Customers, if I filter the sales by date, that also filters my list of customers, so it always looks like 100% of customers are active (because I've filtered out the inactive ones)

I've tried breaking the Interaction between the table and the date slicer, but that affects both customer count columns.

In SQL this is a simple 'left join', but for the life of me I can't find the right BI/DAX incantation, so I assume that I'm making it more complex that it should be and the answer is staring me in the face!

Would some kind user please enlighten me.

Thanks

[Anonymous · Accepted solution]

Hi QuentinB,

I prepared a sample report with a table of customer and the branch and a sales table with the sales amount and the customer name and have uploaded it here.

You need to change the "Cross Filter Direction" to Single. You can change this using Manage Relationships --> Select the relationship between teh sales and customer table --> Click on Edit --> Change cross filter direction to "Single" --> click on "ok" --> click on "Close".

Below is the snapshot of the issue

This is the one with the solution

Hope this helps.

Request you to please mark this as solution, if this is helpful to you.

Regards,

Ashish

[Anonymous · Accepted solution]

Hi QuentinB,

I prepared a sample report with a table of customer and the branch and a sales table with the sales amount and the customer name and have uploaded it here.

You need to change the "Cross Filter Direction" to Single. You can change this using Manage Relationships --> Select the relationship between teh sales and customer table --> Click on Edit --> Change cross filter direction to "Single" --> click on "ok" --> click on "Close".

Below is the snapshot of the issue

This is the one with the solution

Hope this helps.

Request you to please mark this as solution, if this is helpful to you.

Regards,

Ashish

[QuentinB · Accepted solution]

Many Thanks Ashish - starting to make sense now, but of course, there's always 'one more thing' 🙂

If I put a Branch Slicer on and select only Branch 'B', Branches 'A' and 'C' remain listed but with no data - is there a way I can make them disappear as they do when the relationship is 'Both'?

Thanks again.

[QuentinB]

Hmmmm, strangley, checking 'Show Items with No Data' seems to do the trick - what have I done!?

[QuentinB]

That wasn't the right thing, and once I stopped making things complex and applied a 'is not blank' filter, all came right.

Thanks again.