January 6, 2023 at 3:56 pm
Hi,
I am dealing with a Fact Table which has multiple row entries for each customer in respect of the Appointment Types which they have attended. Each row represents an Appointment, and there exists a Column which holds the Appointment Type. What is the best way to find out all those customers which have attended a specific subset of the Appointment Types e.g. X, Y and Z. So if a Customer attended only one appointment e.g. only X or Y or Z he/she will NOT be included; likewise if he/she has attended ten appointments - two of which are, say Y and Z only, he/she will also NOT be included. The customer can have many Appointment Types - not just X,Y and Z - but the requirement is simply that all three must exist for that customer. Kindly advise the best technique to identify the relevant customers in such scenario. Much obliged !
January 7, 2023 at 4:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
January 8, 2023 at 6:53 am
Is this a DAX question or a T-SQL question?
Joe Celko wrote an article showing how to do this in T-SQL. I think the gist of it is that you count the number of parameter values, and the number of unique records returned per group. If the count is equal to the number of values you're looking for, it's a match.
If this is a DAX question... you can use COUNTROWS(FILTER(RELATEDTABLE('FactTable'), [column]="X")>1 && COUNTROWS(FILTER(RELATEDTABLE('FactTable'), [column]="Y")>1 && COUNTROWS(FILTER(RELATEDTABLE('FactTable'), [column]="Z")>1
January 9, 2023 at 3:23 pm
This was removed by the editor as SPAM
January 19, 2023 at 10:02 pm
Assuming your fact table is something like this:
create table ##Temp_CustomerAppointments
(
Customer varchar(10)
, AppointmentType varchar(20)
, Other varchar(30)
)
This query will get you the result: (The list of customers that have appointment of all the type is the set)
select Customer
from (
select Customer, MatchedAppointmentTypesCount = count(*)
from (
select distinct Customer, AppointmentType
from ##Temp_CustomerAppointments
where AppointmentType in ( 'X','Y','Z') --- or whatever set you are looking for
) t
group by Customer
) t2
where MatchedAppointmentTypesCount = 3 --- or whatever number of types you are looking for
January 20, 2023 at 1:42 am
Divided We Stand: The SQL of Relational Division - Simple Talk (red-gate.com)
Read the article... that's how you do it in T-SQL. In DAX, well, that's a whole other kettle of fish.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply