Find customers with no appointments

[Elisa112]

Hello

I am strugglng to find a solution for customers with no appoinment of a certain tye.  I have a customer table and an appointments table joined via customer id (1:Many) , the appoinments table has two types of appointments (Banded, NewAssess), and 3 stages such as first, second, third .  I need to find all customers without NewAssess appointment at all my data looks like this:

Appointments

CustID    MID      Manager     AppDate     Type                Stage

234         696        BIM            8 Nov          NewAssess     First

587         157        MJJ             9 Dec          Banded           Second

Customers

CustID       StartDate      Status      

001              1 Sept         Engaged

234               2 Sept        Engaged

587               5 Oct         Engaged

I would like to find the actual customers with no appointment, not just a count.  I have been plaing around with isblank but this has not worked correctly and I need some guidance.

Thanks in advance

[rajendraongole1 · Accepted solution]

Hi @Elisa112 - create a calculated column in the Appointments table to flag if the appointment type is "NewAssess":

IsNewAssess =
IF(
Appointments[Type] = "NewAssess",
1,
0
)

create a measure to count the number of "NewAssess" appointments per customer:

NewAssessCount =
CALCULATE(
COUNTROWS(Appointments),
Appointments[IsNewAssess] = 1
)

create last measure to identify customers who have zero "NewAssess" appointments

CustomersWithoutNewAssess =
CALCULATE(
COUNTROWS(Customers),
FILTER(
Customers,
CALCULATE(
[NewAssessCount],
ALLEXCEPT(Customers, Customers[CustID])
) = 0
)
)

Hope it works

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[miTutorials · Accepted solution]

Is this the expected output ?

If yes, try the below measure.

NoNewAssess = 
IF(
    CALCULATE(
        COUNTROWS(Appointments),
        Appointments[Type] = "NewAssess"
    ) = 0,
    1,
    0
)

[SachinNandanwar · Accepted solution]

If you want the entire row from the Customers table that dont have appointments just like how its done in SQL with a left join then you can achieve something similar by using EXCEPT in DAX

Table = 

VAR MissingId = EXCEPT(SELECTCOLUMNS(Customers,Customers[CustID]),SELECTCOLUMNS(Appointments,Appointments[CustID ])) RETURN 

FILTER(Customers,Customers[CustID] IN MissingId)

[miTutorials · Accepted solution]

Is this the expected output ?

If yes, try the below measure.

NoNewAssess = 
IF(
    CALCULATE(
        COUNTROWS(Appointments),
        Appointments[Type] = "NewAssess"
    ) = 0,
    1,
    0
)

[rajendraongole1 · Accepted solution]

Hi @Elisa112 - create a calculated column in the Appointments table to flag if the appointment type is "NewAssess":

IsNewAssess =
IF(
Appointments[Type] = "NewAssess",
1,
0
)

create a measure to count the number of "NewAssess" appointments per customer:

NewAssessCount =
CALCULATE(
COUNTROWS(Appointments),
Appointments[IsNewAssess] = 1
)

create last measure to identify customers who have zero "NewAssess" appointments

CustomersWithoutNewAssess =
CALCULATE(
COUNTROWS(Customers),
FILTER(
Customers,
CALCULATE(
[NewAssessCount],
ALLEXCEPT(Customers, Customers[CustID])
) = 0
)
)

Hope it works

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!