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

Filter a table to only contain last row for each user (Like SQL window functions)

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:

bc778c53667fdab7465648d8841e729b.jpg
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...

Screen Shot 2020-08-19 at 09.04.39.png

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...

  1. Removing all filters (with ALLEXCEPT)
  2. Filtering the appointments table to only contain the last row for each contact_id
  3. Applying all filters again (Is that possible?)
  4. Doing DISTINCTCOUNTNOBLANK on that table  


 
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

1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

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.

View solution in original post

2 REPLIES 2
v-xicai
Community Support
Community Support

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.

MFelix
Super User
Super User

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.