Issue multiple date fields in refreshing an aggregate

[PowerRon]

Hi All

having a table with bilions of rows. Want to build an aggregate, which in the end will lead to 300.000 rows.

The aggregate consist of around 10 fields, one of them being settlement-date.
Will refresh this daily by using creation-date. Creation-date is the day the fact was inserted in our data warehouse.
Settlement-date is the date the end-user uses for looking at the data.

 

The same settlement-date can be there on multiple creation-dates.

If I build that aggregate as a view in SQLServer on top of a table,
creation-date has to be part of the view/aggregate because the refresh mechanism in Power Query needs that creation-date.
But then I don't have my aggregate in it's pures form, because I can have the same settlement-date in multiple creation-dates. So, that would mean I have to do a new aggregation again in Power Query to get rid of the level 'creation-date'.

How to solve this? 

Hmm, now writing this I think it can't be solved. Because when I want to aggregate it on settlement-date, I have to rebuild the whole aggregate. Maybe I just have to accept that the aggregate is not completely aggregated and let Power BI do the rest in the context of the visuals.
Am I right?

regards

Ron

[PowerRon]

Haven't got a PBIX , this is a theoretical question. Somewhere coming weeks I will start testing with this

[v-zhangti]

Hi, @PowerRon 

Didn't fully understand your question. Can you provide easy PBIX files for testing and show the output you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.