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

[NT2]

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!

[AllisonKennedy]

@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]

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]

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

[NT2]

Hello, yes the problem is with Helper4 = SELECTEDVALUE('Date'[Date]) when I put it in the visual I got the error, but when I put t in another visual with less columns it works fine.

[Anonymous]

Hi @NT2 ,

Whether the problem has been resolved? If yes, could you please mark the helpful reply as Answered? Thank you.

Otherwise, could you please provide some raw data in your tables(exclude sensitive data) with Text format and the visual settings which the measure [selecteddate] applied on with screenshot? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

Best Regards