Performance issue What If Parameter and DAX queries

[Tanya0806]

We have a requirement where we need to take user input (around 30) and do the calculations based on this user input and render the report.

For illustration purpose , consider a Product table having 5 columns - Product , Product_Type, Number of item, Amount for each item, Total Amount. The table is imported to Power BI.

We have certain graphs based on this table which comes up very quickly.

 There are 4 product and each product has 4 Product Type.We need to take user input for number of items and amount for each Product Type and Product.

	Prodct Type1	Amount	Product Type2	Amount	Product Type3	Amount	Product Type4	Amount
Product1
Product2
Product3
Product4

After the inputs are taken, below calculations to be done:

1. Collect User Input in a measure for item and amount for each product type - 8 measures
2. Check if the number of item in the table is less or the user input - 4 measures for each product type
3. Check if user has not provided any input for item then take the table values - 4 measures for each product type
4. Check if user has not provided any input for amount then take the table values - 4 measures for each product type
5. Claculate the Total Amount from the user input
6. Check the difference between Total amount from user input and the Total amount in table.

Current Design

We have created What If parameter to take user input and 22 calculated measures each dependent on one another.

The report works fine with 3 months of data .

Issue

As we start increasing the months and getting more data , we are facing Out of memory error.

Resource Governing: The memory used by the query exceeded the configured limit. The query or calculations referenced by it might be too memory intensive. Please consider simplifying the query or calculations. If the dataset is hosted on a dedicated capacity/server, you may also reach out to your capacity/server administrator to see if the per-query memory limit can be increased.

Need help to design the report better or to resolve the performance issue.

[lbendlin]

Please install DAX Studio.  Use the Performanc Analyzer in Power BI to isolate the measures/visuals that behave the worst, copy the query to DAX Studio, and examine the query plan.  Then decide how to optimize the measure. You want most of the work in the storage engine, and you want far fewer rows than you will likely see the first time. Rinse and repeat.