Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 name | Count 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 name | Count 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,
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 :
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 !
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 🙂
Let me know how you get on!
🙂
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
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 :
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?
@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.
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |