Forum Discussion
Helper IVMerging tables with large numbers of rows
There's a typo in your expected result - g is appearing 10 times not 9 times. So the total is 911, not 902.
You may want to try TREATAS as an alternative, it will likely perform better with larger data sets
Helper IVI probably didn't explain well enough I think 😛
So in your SUMMARIZECOLUMNS you used GroupIDs[Group] - this is the distinct list of Groups - ie A, B , C, D each listed once.
I don't have the equivilent of GroupIDs[Group] - I have to use my equvilent of Groups[Group] which has each Group appearing multiple times.
In terms of efficiency, I should do it your way - ie have one table with each unique value. If I put Groups[Group] into your formula, obviously it doesn't work as the counts and sums are all wrong.
So what's the best way to get this unique list from an efficiency point of view?
If I create it in PQ by duplicating the table and removing duplicates, it's not efficient as I have 211m rows.
Would writing it in DAX be better as a calculated table? Would this impact me using it in the formula?
Essentially - how do I create a table with just distinct values of Group so I can use it in the measure? - in the most efficient way 🙂
- lbendlin
Super User
I see. A DAX calculated table (using VALUES for example) is more efficient but you may run into circular reference errors. Try it out.