Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have an odd situation here.
So I have a direct query fact table (connected to our data warehouse) and several dimension tables in my Power BI setup. This is the error I get when I put this measure into my visual
Here are some more information on this measure. The code of this measure is as follows:
Sch Start Period Measure =
VAR CP = SELECTEDVALUE(Fact_Table[Control Point Number])
VAR DelID = SELECTEDVALUE(Fact_Table[Deliverable ID])
VAR enddate = SELECTEDVALUE(Fact_Table[Schedule Date])
VAR startdate =
IF( CP > 1,
CALCULATE(
MIN(Fact_Table[Schedule Date]),
Filter(ALL(Fact_Table), Fact_Table[Deliverable ID] = DelID),
Filter(All(Fact_Table), Fact_Table[Control Point Number] = CP-1)
),
enddate
)
RETURN
CALCULATE(
Min(Date_Table[Period End Date]),
Filter(Date_Table, Date_Table[Period End Date] > startdate)
)
//startdate
If you notice i have //startdate at the bottom. I was testing if anywhere else in my code there were issues and there wasn't (ie the startdate formula in the measure works as intended). The only part that seems to be giving me an error is the calculate after the RETURN.
Note that the table names were altered and the error was partially redacted for privacy concerns
Any ideas on what might be causing this?
Solved! Go to Solution.
Hi @AlvinLy
Your DAX measure is quite complex and combines data from different contexts. In DirectQuery mode, not all filtering actions behave as they would in an in-memory model (Import mode). Here are some points to consider:
Complex Filters: The filters and calculations you're applying might be too complex or might result in a query that SQL Server (or whatever your backend is) cannot process efficiently or at all. In DirectQuery mode, each DAX expression translates into a SQL query executed on the database, and some SQL servers have limitations on subqueries or complex joins.
Filter() Function Over DirectQuery: Using 'Filter(ALL(...))' in DirectQuery mode can sometimes lead to performance issues or limitations because it requires scanning the entire table. The combination of these filters might be generating a query that is not supported or is too complex.
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @AlvinLy
Your DAX measure is quite complex and combines data from different contexts. In DirectQuery mode, not all filtering actions behave as they would in an in-memory model (Import mode). Here are some points to consider:
Complex Filters: The filters and calculations you're applying might be too complex or might result in a query that SQL Server (or whatever your backend is) cannot process efficiently or at all. In DirectQuery mode, each DAX expression translates into a SQL query executed on the database, and some SQL servers have limitations on subqueries or complex joins.
Filter() Function Over DirectQuery: Using 'Filter(ALL(...))' in DirectQuery mode can sometimes lead to performance issues or limitations because it requires scanning the entire table. The combination of these filters might be generating a query that is not supported or is too complex.
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
92 | |
82 | |
70 | |
64 |
User | Count |
---|---|
115 | |
106 | |
96 | |
81 | |
72 |