Simple query, exceptionally slow FE performance

[Anonymous]

I'm really struggling with exceptionally slow performance in Desktop and need help on figuring out where the issue is

It's taking 300ms to calculate one Measure, with 80% of the time in the formula engine (if that makes any difference to the solution). There are 42 million records in the biggest line of the query plan.

It's not a lot of data, and it can't be inefficient DAX that's making it take 5 minutes. Has to be something else but I don't know what else to do.

Where do I look?

This is the SQL for the slowest step as measured by SE timings - I can't see FE timings in DAX Studio

SELECT TOP (1000001) 

[t5].[Created Date],[t5].[Appointment Start],[t5].[Activity ID],[t5].[Adviser Relationship Type],[t5].[Appointment Subject],[t5].[Appointment Description],[t5].[Appointment Status],[t5].[Created By],[t5].[Owner],[t5].[Owner Region],[t5].[Appointment Duration],[t5].[Associated Parties],[t5].[Associated Party Count],[t5].[Organiser Region],[t5].[Adviser CON],[t5].[Adviser Name],[t5].[Booked By],[t5].[Hub],[t5].[Owner Memeber Type],

COUNT_BIG(*) AS [a0]

FROM 
(
(select [activity_created_date] as [Created Date],
    [activity_due_date] as [Appointment Start],
    [activity_id] as [Activity ID],
    [activity_contact_adviser_relationship_type] as [Adviser Relationship Type],
    [activity_subject] as [Appointment Subject],
    [appointment_description] as [Appointment Description],
    [activity_status] as [Appointment Status],
    [activity_createdby] as [Created By],
    [activity_owner] as [Owner],
    [activity_owner_region] as [Owner Region],
    [activity_scheduled_duration_minutes] as [Appointment Duration],
    [activity_associatedparty] as [Associated Parties],
    [activity_associatedcount] as [Associated Party Count],
    [activity_organiser_region] as [Organiser Region],
    [activity_contact_code] as [Adviser CON],
    [activity_contact_name] as [Adviser Name],
    [activity_booked_by] as [Booked By],
    [member_type] as [Member Type],
    [owner_member_type] as [Owner Memeber Type],
    [hub] as [Hub]
from [mart].[sales__appointments] as [$Table])
)
 AS [t5]
WHERE 
(
(
([t5].[Member Type] IN (N'bda',N'ebdm',N''))
)
 AND 
(
1 = 1
)
)

GROUP BY [t5].[Created Date],[t5].[Appointment Start],[t5].[Activity ID],[t5].[Adviser Relationship Type],[t5].[Appointment Subject],[t5].[Appointment Description],[t5].[Appointment Status],[t5].[Created By],[t5].[Owner],[t5].[Owner Region],[t5].[Appointment Duration],[t5].[Associated Parties],[t5].[Associated Party Count],[t5].[Organiser Region],[t5].[Adviser CON],[t5].[Adviser Name],[t5].[Booked By],[t5].[Hub],[t5].[Owner Memeber Type] 

[Anonymous · Accepted solution]

The issues here were Azure setup and wrongly using measures instead of custom or calculated columns. We imprpoved data distribution and caching in the dwh and re-wrote a couple of measures as calculated columns (we're just learning...) and the problem went away

[parry2k]

@Anonymous I'm open to look at it if you can share pbix file, remove any sensitive information before sharing, also share what you are trying to achieve.

[Anonymous]

Hi @parry2k  - that's very generous, thank you

We're using DirectQuery - does that have any impact on your ability to replicate the issue, given I can't share security permissions to query the data warehouse?

[v-lid-msft]

Hi @Anonymous ,

We can try to use the following formula which avoid using MAXX:

Number of appointments attended previous day =
// get the date of the current row in the output table
VAR _date =
    MAX ( Appointments[Appointment Start] )
VAR _type =
    DISTINCT ( Appointments[Member Type] ) // get the most recent prior date from the whole appointments table
VAR _prevdate =
    CALCULATE (
        MAX ( Appointments[Appointment Start] ),
        ALLSELECTED ( Appointments ),
        Appointments[Member Type] IN _type,
        Appointments[Appointment Start] < _date
    )
RETURN
    CALCULATE (
        [Number of appointments],
        ALLSELECTED ( Appointments ),
        Appointments[Member Type] IN _type,
        Appointments[Appointment Start] = _prevdate
    )

If it does not meet your requirement, could you please share the formula of measure [Number of appointments] ?

Best regards,

[Anonymous]

Thanks @v-lid-msft - I appreciate you writing to help me with this

I've heard in another post that ALLEXCEPT is a bad function to use because it's very inefficient, so I'm focusing on that now. I also heard MAXX is a good function to use, while you suggested it might be a problem. Bit confused!

Anyway - thanks for replying, I'll try your solution.

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[parry2k]

@Anonymous if you are using direct query then I will not be able to replicate. Maybe try to import and then share pbix file. How big is the dataset?

Performance issues can be for many reasons so without looking into pbix, it is bit shooting in the dark which I hate.