Adding Subset table to Fact Table Issues - exceeded maximum & too complicated to run

[GritsNGravyyyy]

Hello everyone! Been using this community and others online for some time, but this is my first post for help. Appreciate any insight/guidance/recommendations you all may have to share. 

We use Snowflake with very large tables on our customer/sales data, and powerbi to build reports to showcase. 

For this situation, using PowerBi Desktop - we have a fact table (TRN_PROMO_CORE) on transactions/sales/units/etc. by indiv_key per customer - a large table (SEASONAL) of 378333926+ rows. Now we are trying to add a subset table into powerbi for those customers we have identified as making purchases during a specific promo season, the table row size is 15944750. 

- Those in the SEASONAL table will be in the TRN_PROMO_CORE Table (except for I think like 5-6 nulls), while the TRN_PROMO_CORE table obvioulsy has many other rows not in the seasonal. See below for relationship and how setup. 

- The only column that is in both is Indiv_key or indiv_brand_key, and the seasonal table is bringing in the columns for the seasonal profiles on those indiv_brand_key (s)

Both tables are in direct query mode, but I also tried doing import mode and nothing fixed. 

At first, couldnt get the table relationship to work, but I found that the table the team created had two identical indiv_key we were building the relationship on, which was causing the issue. Addressing this issue from a recent brand merge, we added an indiv_brand_key column to the table, which has just one row per each - which can be matched to the same indiv_brand_key in the main fact table. Great, did a many to one relationship, which worked without errors. Good to note, the now not used indiv_key still has 2 indiv_key per each though, but since connecting on brand_indiv_key now, technically only one per row. Don't think this is causing an issue, but figured would note. 

After doing the relationship, I did a simple test using a measure from the main PROMO_CORE table for Customers Net 

Customers Net = CALCULATE([Customers Gross], VW_POWERBI_TRN_PROMO_CORE[RETURNED_QTY]<>VW_POWERBI_TRN_PROMO_CORE[UNITS_GROSS])

Filtering only on Holiday 2023 season from the SEASONAL table. 

Then I get an error that I have exeeded the allowed size "Couldn't load the data for this visual. The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows". 

So, after researching I attempted to limit rows like Guy in a Cube says here https://www.youtube.com/watch?v=_zYvybVMk7k limiting rows and then adding a parameter for. Here is the advanced editor end piece to showcase: 

Now, once I did this I got another error for exeeding limit, so I applied the same steps to the SEASONAL table as well. 

Same error didn't occur, but another error I have not seen in a while happened "Couldn't load the data for this visual. OLE DB or ODBC error: This query is too complicated to run... "

Not really sure where to go from here, and there doesn't seem to be much guidance online. I thought maybe it was the measure, but the measure is a relatively simple one. 

I also attempted to just publish a report to test online and see if that exceeded limit went away, but the issue even occurs on the published report... 

What else should I test? Any recommendations? I feel like this should be a simple one, but been knocking my head trying to get to work. 

P.S. if you read all of this, you rock! 

If anyone knows a way to be able to copy the text from PowerBi errors, please do let me know. I always end up having to write it out when searching the web for others with the issue, but would be awesome if I could actually copy the error text (like you can with Dax errors in the little yellow box that shows at bottom) 

[GritsNGravyyyy]

Thanks Vanessa for the response! Really appreciate your time. 

So yes, we need a complete overhaul on our main semantic model, which management is aware of. But with the person who made and previously managed departing last month, things have been a bit stalled on that. Hoping to have approval soon for fixing and doing a complete overhaul, as we are just putting temporary band-aids on currently. 

However, in the meantime, I was able to find a solution. Figured I should share here, in case anyone else has the same issue/situation... 

I was able to find that the original tables in our model were identified under a different warehouse from the tables we were adding, even though they all come from the same DB and WH. Took some digging, but I noticed in Model View, that the original tables had a different color from the new tables I added (example photo below, see top of each table for color of blue and purple): 

Seemed unusual, as all coming from the same WH, so I then looked in power query and found he had created a parameter to label the WH as PBI Warehouse, which added this on all those other tables when I right clicked for advanced editor: 

I changed the parameter to the correct WH, and boom all table colors became the same and it now works. 

Two funny things though: 

1. at first I got an error on one of our time tables, so I turned off load on that table and it then worked. Then I turned load back on and it worked for that table. No idea why, but it worked. Then I walked my colleague with ownership of the model through everything and tested on all tables again, without turning off load on that table that threw my error, and for some reason it then worked for him without having to do my workaround. 

2. While this seems to solve the issue, I have another report that works just fine where I connected one warehouse on two tables to another warehouse, and I never ran into an issue. I was able to get the relationships and everything to work. Maybe the issue was just stemming from tables in the same warehouse with different labeled warehouses causing an issue, otherwise, not sure why it didn't cause an issue on the other report connecting one different warehouse to another different one. 

Welcome any thoughts you might have, but all good otherwise. Hope this helps someone else as well. 

Cheers!

[vanessafvg]

you are going to need to optimize your model, there is something about your model isn't working i would say as is returning so many rows, even if you have a lot of data, when you aggregate it you shouldn't need to return that many rows on a visual.   what are you running?  more information is required to understand what is happening, have you run performance analyzer?   have you ever used dax studio?