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

[H_insight]

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 simple model:

In my page I have slicers from DIM_GEOGRAPHY (State and City) and I can't use import mode as it will take forever.

Goal: I want to table or a virtual table that I can use without hitting the 1M rows error so I start to cluster my sales data.

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

Greatly appreciate any help.

[amitchandak]

@H_insight , You can create an aggregated table in DB or in import mode

https://learn.microsoft.com/en-us/power-bi/transform-model/aggregations-advanced