Hi @Anonymous
The error you're encountering is likely due to the high memory requirements of the `LOOKUPVALUE` function and the complex operations involved when generating a calculated table. This often happens when there are a large number of rows in the tables being referenced. Here are some things you should consider regarding the formula:
### Issues in the DAX Formula:
1. **Memory Overuse**: The `LOOKUPVALUE` function can be quite memory-intensive, especially when working with large datasets. When used in combination with `SELECTCOLUMNS`, it results in the creation of multiple virtual tables, which can significantly increase memory consumption.
2. **Calculated Tables**: Calculated tables are computed and stored in memory, which means they add a considerable overhead. When using such operations across a large dataset, it might be better to avoid creating calculated tables in DAX if possible.
### Alternatives to Optimize Performance:
1. **Data Modeling Optimization**:
- Instead of creating a calculated table, consider adjusting the existing data model. It might be possible to achieve your desired result using relationships between tables and calculated columns instead of building an entirely new table.
- Consider normalizing your model if there are redundant fields, which could simplify calculations and reduce memory use.
2. **Replace `LOOKUPVALUE` with Relationships**:
- Instead of using `LOOKUPVALUE`, ensure that you have a relationship defined between `Global Data` and `ContractEmployee` tables. If they are related by a common key, you could simply add the fields from the related table to your visual or calculated column without resorting to `LOOKUPVALUE`.
- Creating measures that aggregate the data based on these relationships may provide the same output more efficiently.
3. **Power Query Transformation**:
- Perform the data transformation in Power Query rather than in DAX. You could use Power Query to merge the `Global Data` table with the `ContractEmployee` table. This will allow you to perform lookups without consuming additional memory during runtime since Power Query transformations happen before the data is loaded into the model.
4. **Virtual Tables with Variables**:
- Consider using DAX variables to store intermediate results and reduce redundant calculations. This approach might help with simplifying the logic and reducing memory overhead.
Example:
```DAX
Global Data Valid =
VAR EmployeeNames =
ADDCOLUMNS('Global Data',
"EmployeeName", LOOKUPVALUE(ContractEmployee[Contract_EmployeeName], ContractEmployee[Contract_EmployeeName], 'Global Data'[Contract_EmployeeName])
)
RETURN
SELECTCOLUMNS(EmployeeNames,
"EmployeeName", [EmployeeName],
"Process", 'Global Data'[HR/Employee],
"StartDate", [Expected Create Date],
"EndDate", [Expected End Date],
"OverallStatus", 'Global Data'[Overall_status],
"Country", 'Global Data'[Country],
"Hire_Date", 'Global Data'[Hire_Date]
)
```
This approach can sometimes help reduce the computational load since intermediate steps are calculated and reused.
5. **Aggregation Logic**:
- Evaluate whether you need a calculated table at all. If the desired outcome is just for visual representation or analysis, consider creating DAX measures that can be calculated on the fly, rather than storing data in a new table.
6. **Database-Level Summarization**:
- If none of the above options work and you still face memory limitations, consider summarizing the data directly at the database level. You can create a **view** in your database that performs the necessary joins and aggregations, which can then be imported into Power BI as a summarized data source. This approach reduces the memory load in Power BI and allows you to handle large datasets more effectively.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile
