GroupBY/Summarize Performance

[tigels]

I have follow DAX to remove duplicate data in my fact table (I've tried these two ways):

Dedup_Qt = SumX(AddColumns(Summarize(Table;Table[keyOfMyTable]);"Total";Calculate(Sum(Table[qt]));"Count";Calculate(COUNTA(Table[keyOfMyTable])));DIVIDE([Total];[Count]))

Dedup_Qt = SumX(GroupBy(Table;Table[keyOfMyTable];"Total";SumX(CurrentGroup();Table[qt]);"Count";COUNTX(CurrentGroup();Table[keyOfMyTable]));DIVIDE([Total];[Count]))

My table has about 100 millions of rows.

My key can duplicate of many ways (one row in two, three, four..), and one filter on my report can change it (it was three and is just two now), so I need to remove it dinamically.

I need to improve the performance of these DAX. I always get memory error.

"You have reached the maximum allowable memory allocation for your tier".

Are there other way to do it?

[v-jiascu-msft]

Hi @tigels,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[Seward12533]

The SUMX's are killing you. Your best bet is to remove the duplicates in the query editor before it gets loaded into PowerBI.