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

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

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'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?

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    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.

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity 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
        Icon for Community Support rankCommunity 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,