Cluster Stores based on Sales value in a Direct Query Mode - Avoid row Limit of 1000000 rows

DAX
1

Hiya,

In my PBI model (Dual Mode), I have a couple of Dimensional tables (imported) connected to a single fact table (~700m rows via Direct Query) and they have one-to-many relationships and use a single cross filter. I am having some difficulties creating clustering measures or virtual table for Stores by their Sales due to the large volume of data in my fact table.

Below is the model:

image

Goal:
I want to table or a virtual table that I can use without hitting the 1M rows error so I can cluster my Stores based on the Sales data. In PBI, my page has slicers from DIM_GEOGRAPHY (State and City).

The clustering option is "if FACT_CONSUMER[Sale Value] is between 0 to 33 then “poor”, 34 to 66 then “Good”, the rest would be “Top”.

Greatly appreciate any help.

2

@AdamK You will have to rely on Aggregations feature and create aggregated views in Database for optimization.

3

@AntrikshSharma

I was trying not to think of that :slight_smile:

What’s your thought if I used Dataflows instead?

4

Hi @AdamK

Due to inactivity, a response on this post has been tagged as “Solution”.

If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,.

We hope you’ll give your insights on how we can further improve the Support forum. Thanks!