Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Count of non-contacted customer incorrect

Hi everyone, i hope you doing well 

 

I'm trying to calculate the number of customers who has not been contacted.

I have a fact table which contain the data for the appointment and another table with all the customers.

In the appointment_table there is a dimension based on the type of appointment (physical or called) which will be important later.

 

To count the number of contacted customers is rather simple :

# contacted customer = distinctcount(appointment_table[IDCustomer])

 

The total number of customer in the customer table :

# total customer = calculate(distinctcount(customer_table[idcustomer]),all(calendar))

 

Then to count the number of non-contacted customers i do this :

# non-contacted customer = [# total customer] - [# contacted customer]

 

And in appareance it works well. The issue start with two things :

I have a table visual where i list the name of the customer and then i filter this table based on the # non-contacted customer to keep only those who are equal or superior to 1.

If i have no other filters based on my appointment table it works well. But if i filter on the type of appointement (let's say to identify the customers who has never been contacted physically) it will only keep the customers who have at least one appointment in the fact table but won't count (only in the table visual) the one who never had any kind of appointement.

 

I don't know if it's clear...

here's the representation of the issue :

 

Without filters :

Customer nameCount of non-contacted customer
Customer 1 (never visited at all)1
Customer 2 (only physical)0
Customer 3 (only called)0
Customer 4 (both)0

 

With filter on "physical only" :

Customer nameCount of non-contacted customer
Customer 1 (never visited at all)empty (when it should be 1)
Customer 2 (only physical)0
Customer 3 (only called)1
Customer 4 (both)0

 

Any idea where this could come from ?

Thank you,

best regards,

6 REPLIES 6
Anonymous
Not applicable

I have further information and an apologize because i didn't seek through the whole thing,

I do have a tricky situation where i count the number of customers with a userelationship otherwise i can't filter on a certain table 

 

Basically both table are from different database but share some info, like the salesmanID (which is actually different on both base but i make a correspondance table between them and one of the relation must be inactive...

 

I'm trying other things to see how i would fix it, 

Here's the whole picture :

NotFreddy_0-1634843623853.png

Then in this configuration i can get the result correct but only if i filter on both salesmanID filter (from table_secteur (correspondence table) and the customers table (table_client)

 

Basically i just went to filter through  only one filter to see calculation between visits and base, based on the salesmanID, if have any guess !

 

ajaydavidluke
Helper I
Helper I

Hi @Anonymous 

 

If my understanding is correct, you could bypass a DAX and use a matrix table, populate your 'Rows' with customers then insert your non contacted field in 'Values' as a count or distinct count.  I personally try and avoid filters as this can throw up a number of issues later down the line 🙂

 

ajaydavidluke_0-1634744961679.png

 

 

Let me know how you get on!

 

🙂

goncalogeraldes
Super User
Super User

Hello there @Anonymous ! Not sure if this will help but try adding a CALCULATE to the # contacted customer measure, like so:

# contacted customer = CALCULATE( DISTINCTCOUNT ( appointment_table[IDCustomer] )

Otherwise the DISTINCTCOUNT may ignore the filtering context!

 

If you want to avoid empty space just add a "+0" to the end of the first two formulas, like so:

# contacted customer = CALCULATE( DISTINCTCOUNT ( appointment_table[IDCustomer] ) + 0

 

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

Anonymous
Not applicable

Hi Gonçalo, 
Thank you for your time and your advice, i tried it but it doesn't solve the issue.

Here's i managed to make a screen without critical data to expose the problem :

explanation.png

 

 

I think this come from the fact that since a customer never appeared in the appointment table, as soon as i apply a filter on this table it only keep the customers who are "well-linked" between both table. 

@Anonymous what is the relationship between your appointment_table and customer _table?

Anonymous
Not applicable

@goncalogeraldes here's my model focused on the appointment_table (the red one) and customer_table (the blue one), the others relationship are not used in this case and should not impact the calculation. I tried bilateral relationship but it makes more error and cause performance issue.

model.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors