Any Other Workarounds for DAX Out of Memory?

[WinterMist]

Hello Community - 

Any guidance would be greatly appreciated.

I've run into this DAX measure "out of memory" error multiple times (due to being a column in a table visual) in the past & we are usually able to get around it using one of the following items:

- Require users to make a slicer selection (so the measures can work on a subset of data).

- Move measure columns outside of the table visual (so as to calculate at a higher level).

- Move measure calculations out of DAX and into an earlier transformation layer (like a Data Warehouse).

Unfortunately, in this case, business requirements make all 3 of the above workarounds impossible.

So if anyone happens to have any other ideas to make this work, that would be amazing!

The report works as desired when a single slicer value is selected (e.g. State = Indiana).

However, requirements dictate that the user MUST be able to see report results even when no slicer values are selected.

The PBIX is available here...

https://drive.google.com/file/d/1W2zlKfyBdAh1_uDIkAHKDLi4z276VOpN/view?usp=sharing

The model is as follows:

The report is really just the "Main" page.

The secondary "Investigation Only" page is just there to see more clearly how the measures are working for an individual unique "combo" of State+County+Crop+Plan.

Below, you can see that when State = Indiana, the main table visual shows data.  (Screenshot 1)

You can also see that when no slicer values are selected, the main table visual does NOT show data.  (Screenshot 2)

NOTE: State, County, Crop & Plan are all regular columns from tables in the model.

The other columns in the table visual are all measures.

Here is a brief logic summary for each measure.

- Loss Ratio ALL (calculates the Loss Ratio across ALL years for each unique "combo" of State+County+Crop+Plan)

- Loss Ratio YTM (calculates the Loss Ratio for the specific selected value in the "Year to Model" slicer within each unique "combo")

- Premium YTM (calculates the Premium for the specific selected value in the "Year to Model" slicer within each unique "combo")

- Premium YTM Adj (calculates the adjusted Premium by multiplying: Premium YTM * Selected Value in "T3 Premium ADJ" slicer.

- T1&T2 (returns "1" only if both T1 & T2 are "1" ELSE 0)

- T1 (returns "1" only if Loss Ratio ALL > selected value in "T1 Loss Ratio ALL" slicer threshold)

- T2 (returns "1" only if Loss Ratio YTM > selected value in "T2 Loss Ratio YTM" slicer threshold)

Summary of why typical workarounds are not possible here:

1) Users MUST be able to see ALL rows across the entire data set without filtering State, County, Crop or Plan.

2) Users MUST then be able to change the 3 threshold slicers (T1, T2 & T3) to affect the rows avaible & the data shown.

   NOTE: The table visual filters out any rows where T1&T2 are not "1" (i.e. TRUE).

3) Users MUST be able to see the measure calculated values at every row in the table visual so that they can see how the thresholds will impact the rows being shown.  (BIG PROBLEM, I know.)

3) Since the measures in the table are dependent upon dynamic selected values from the threshold slicers, I do not believe it's possible to move these measures into a Data Warehouse or earlier transformation levels that have no awareness of Power BI selected slicer values.

CONCLUSION: Based on my experience, I believe that Power BI is not capable of meeting business requirements in this case. 

It simply cannot handle calculating measures for thousands of rows within a table visual.

However, I wanted to exhaust all possibilities by asking forums just in case I am mistaken.

If you need any further explanation of the report or its measures, let me know.

Thank you very much for your time.

Sincerely,

Nathan

[sjoerdvn]

You could skip loading that fact id column. It is high cardinality and I don't see you need it. Should save some memory.

[lbendlin]

Use TREATAS to transfer filters between unconnected tables.

03B Loss Ratio YTM = 
CALCULATE(
        [03A Loss Ratio ALL],
        TREATAS(VALUES('D Year to Model'[Year to Model]),'D Year'[PolicyYear_Int] )
    )

(for example).

I am not able to reproduce the error - which selections are supposed to cause it?

[WinterMist]

@lbendlin 

Thanks for your response.  

To reproduce the error, select:

- All States

- All Counties

- All Crops

- All Plans

Regards,

Nathan

[lbendlin]

You are going over the cliff when you add the plan name.

FactRMA: 1578328

CountyState: 3194

D Crop: 7394

D Plan: 3716

Before adding the plan you are at 

37275757486552  rows. 

After adding it in  you are at 

138516714820027232 rows.

That's likely too much. It fits into a Int64 integer but not a Int32.

You need to find ways to keep the cardinality down, for example by making some of the dimensions be dependent on each other (crops per state, or plans per crops, for example).  If you cannot do that then yes, Power BI is not capable of what you are trying to achieve.

[WinterMist]

@lbendlin 

Thanks for confirming.

So I realize this is a Power BI forum.

But for something like this, when PBI simply cannot meet the need, are there other tools you'd recommend?

Thanks again for taking the time to respond.

Nathan

[lbendlin · Accepted solution]

If you have access to Tableau or QlikSense you can give those a try.

[WinterMist]

@lbendlin - Thanks again for all your time!