I have a rather complex Semantic Model with over 20 dimensions. At the heart of it I have a Direct Query detail table and an imported aggregation table as my facts. The detail table has daily dates and the aggregation table is by month. These work great if I have a separate month dimension to my day dimension.

However, that is not very user friendly.

So how can I combine the date dimension so they both use the same dimension but if a user selects month then it aggregates and if they select day it doesn’t.

I have Fact which I count rows for number of tickets.

Then I have used_date as my calendar and use_month as my month dimension.

I can get this to work if the 2 dimensions are separated. I have connected month to the calendar date dimension but the model no longer uses aggregation then.