Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hey Guys,
I am trying to write a query and I'm stuck.
I have an appointments fact table that shows booked appointments. One user (called contact in the data) can book multiple calls. For example if they cancel or if they don't pick up we can book another call for them.
The data looks something like this:
I need to analyse how many people have booked a call and what the outcome was. Unfortuantely the fact that a contact can book multiple calls messes up the data and the numbers don't add up.
For example, my measure is...
Appointment Contact Count = DISTINCTCOUNTNOBLANK(Appointments[Contact Id])
And the result is...
The issue is of course that for each row, there is a different filter context and if a contact has multiple rows with different states the person will be counted for multiple call_statuses.
I think it can be solved by...
I am right now stuck at the 2nd step. In SQL that type of thing is super easy and can be done like this:
with last_appt_only as (
select
row_number() over(partition by contact_id order by created_at desc) as contact_appt_num,
count(*) over(partition by contact_id) as contact_appt_count,
*
from analysis.base_acuity_appointments
)
select
call_status,
count(*)
from last_appt_only
where
created_date >= '2020-04-27' and created_date <= '2020-05-03' and
appointment_type_broad_int = 100 and
contact_appt_num = 1
group by call_status
But even after a lot of googling I couldn't find the equivalent of window functions in DAX.
My attempt was to try something like this:
EVALUATE
VAR last_contact_row_ids = SUMMARIZE (
Appointments,
Appointments[Contact Id],
"MaxAppointmentId", MAX ( Appointments[Appointment Id] ),
"ItemCount", COUNTROWS(Appointments)
)
VAR last_rows = FILTER(Appointments, Appointments[Appointment Id] IN last_contact_rows[MaxAppointmentId])
RETURN
DISTINCTCOUNTNOBLANK(last_rows[Contact Id])
But it's not working and I don't think that's the right direction. There must be an easier way to do something like this in DAX. Could someone please point me in the right direction please?
Thank you
Solved! Go to Solution.
Hi @Anonymous ,
You may create a new measure like DAX below.
Appointment Contact Count_New =
VAR _table =
SUMMARIZE (
Appointments,
Appointments[Date Status],
Appointments[call_status],
"_Value", [Appointment Contact Count]
)
RETURN
IF (
HASONEVALUE ( Appointments[call_status] ),
[Appointment Contact Count],
SUMX ( _table, [_Value] )
)
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
You may create a new measure like DAX below.
Appointment Contact Count_New =
VAR _table =
SUMMARIZE (
Appointments,
Appointments[Date Status],
Appointments[call_status],
"_Value", [Appointment Contact Count]
)
RETURN
IF (
HASONEVALUE ( Appointments[call_status] ),
[Appointment Contact Count],
SUMX ( _table, [_Value] )
)
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
In this case you need to add an aggregator function to make the sum of your values so you need to use something similar to this
Appointment Contact Count =
IF (
HASONEFILTER ( Table[Date Status] ),
DISTINCTCOUNTNOBLANK ( Appointments[Contact Id] ),
SUMX ( Table, DISTINCTCOUNTNOBLANK ( Appointments[Contact Id] ) )
)
Not sure how your column called that you are using in the Matrix (Sucessfull, Failed cal, ...) but the Date Status that I refer should be that column.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |