AAS model with MAX measure (text field) failing when fact table connects to many dims

[Karlalm]

Hi everyone 🙂

I have an AAS model with one sideloaded excel table.

The model contains a star schema. The fact table has over 40million rows with 50+ columns and there are several dim tables.

I am trying to replicate a visual (a table) for a client that currently does it in excel sourcing the data from a different place and now wants to source the data from our model.

The first thing I did was filter down the data and add the fields to the table that the user has on their excel.This generates a visual that contains less than 6500 rows and less than 25 columns that come from the model. This works fine.

On top of this, the user generates over 18 new columns in excel based on the original columns. Since I have a big fact table, I decided to create measures instead of adding new columns.

Some of the measures I am creating are based on text fields, for example this is one of their excel formulas.

IFERROR(MID(I137,FIND("DUTY FREE",I137,1),9),"")

When I replicate this in DAX, I use an aggregated function like MAX to get the row context value for the column corresponding to I137 and this is where I face the issue, Power BI is unable to compute this and throws two errors (i haven´t been able to determine when it throws one or the other).
- Resources exceeded

- Couldn´t load the data for this visual

When I check the server it is ok, so the resources exceded is not an issue with the AAS server and for the second error I know the visual contains less than 1,000,000 as I am not adding rows, just columns.

We did a workaround where we add the function of HASONEVALUE to the measure and it works, meaning it shows a result. However the issue with this workaround is that as I add more measures, they are taking too long to load, so by the time I add the last measure, it takes over 2 minutes to load.

Has anybody else faced this? And how have you solved ?