Forum Discussion

NT2's avatar
NT2
Frequent Visitor
1 year ago

Power BI Visual Error: "Query Execution Has Exceeded the Allowed Limits" When Using Date Measure

Hello, I need help with an issue in Power BI. I have a visual that includes several columns along with the following measure:

 

 

 

selecteddate = IF(ISBLANK(SELECTEDVALUE('Date'[Date])), TODAY(), SELECTEDVALUE('Date'[Date])) //Used in a slicer
RETURN IF(
    SELECTEDVALUE('sales'[Transdate]) <= selecteddate,
    MAX('sales'[startdate])
)

 

 

 

 

error: "Query execution has exceeded the allowed limits."

If I remove this measure from the visual, the data displays correctly without any issues. Additionally, when I don't select any date from the slicer, the visual works fine, even with the measure included (Default date is today()).

Could this be a performance issue with the DAX formula, or is there a better way to write this logic? Any help on optimizing or troubleshooting would be greatly appreciated. Thanks!

Need Help

5 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    1 year ago

    NT2  Am I reading your measure correctly that you have 2 date tables? Can you share a screenshot of your model relationships view for the three tables in your measure? Also include any other tables that are being referenced in the visual that times out. 

     

    I suspect the error is due to a lack of relationship between the date table and your sales table and it's trying to create a record for every sale on every date. 

    • NT2's avatar
      NT2
      Frequent Visitor
      1 year ago

      Hello, thank you for your response. In the visual, I am only displaying data from the Sales table, and the Date table is not linked to the Sales table. In the report, I have a slicer for the Date table that is used in the calculation of the measure if the SelectedDate of the slicer is bigger than the Transdate the measure will returns the max of the column Stardate.

       

      • AllisonKennedy's avatar
        AllisonKennedy
        Community Champion
        1 year ago

        NT2 I can't see why that would be working when no date is selected but time out when you select a date. 

         

        Can you try breaking down the measure into parts and see if each part will work? So create some helper measures:

         

        Helper1 = MAX('sales'[startdate])

         

        Helper2 = SELECTEDVALUE('sales'[Transdate])

         

        Helper3 = IF(ISBLANK(SELECTEDVALUE('Date'[Date])), TODAY(), SELECTEDVALUE('Date'[Date]))

         

         

         

        Helper4 = SELECTEDVALUE('Date'[Date])

         

         

        Test these each individually in your table, see if they return your expected result with a date selected. Let us know your findings.