Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Simple query, exceptionally slow FE performance

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 formu...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
v-lid-msft
Community Support
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      v-lid-msft
      Community Support
      6 years ago

      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,