Forum Discussion

Don-Bot's avatar
Don-Bot
Icon for Helper V rankHelper V
2 years ago
Solved

Aggregation by month connect to date dimension

So this may be a beginner type question but I am having issues with this.

 

I am creating an aggregation import table that I want to be by month.  However, my calendar table is actually by date time for the fact table.  

 

How can I connect it so users can use the date dimension table for the calendar yet it fires off the aggregation.  

 

I tried putting a Month Date in the dimension and then connect that to my aggregation it yells and says the aggregation can't have a bi-direction filter on it.  

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    It is bi-directional becuase there are 'many' rows in the date dimension for a single month. You would need to do 1 of 2 things (there might even be more than 2 🙂
    1. Create a view of Date Dimension to only have one row per month and join on view
    2. In the aggregation table, add a column for the first day of month (or last day of month) and join on date dimension by this new column (1/1/2021 or 1/31/2021)

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    It is bi-directional becuase there are 'many' rows in the date dimension for a single month. You would need to do 1 of 2 things (there might even be more than 2 🙂
    1. Create a view of Date Dimension to only have one row per month and join on view
    2. In the aggregation table, add a column for the first day of month (or last day of month) and join on date dimension by this new column (1/1/2021 or 1/31/2021)

    • Don-Bot's avatar
      Don-Bot
      Icon for Helper V rankHelper V
      2 years ago

      I did exactly what your #2 suggestion is.  Seems to work like a charm.

       

      Thanks!

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Awesome!!!

    • Don-Bot's avatar
      Don-Bot
      Icon for Helper V rankHelper V
      2 years ago

      Hi Anonymous , I was looking at this again and have a follow up question.  

      If I went with option 1 do you have any recommendations on how I could reconcile what would end up being 2 date dimensions?

      1 would be the calendar date dimension that has every day in it.  The 2nd would be the monthly one you suggested above.  

      Thanks

    • Clampazzo's avatar
      Clampazzo
      Icon for Resolver I rankResolver I
      2 years ago

      Hi Anonymous I'm attempting #2 above and it's not working.  I'm unsure as to why.  

      I have the typical detail table and aggregation table.  Detail is by datetime and aggregation is by month.  

      I created a custom id column that is the BOM I then connected that to the datetime ID on the Date Time dimension in the model.

      I then did a manage aggregation and pointed the new custom field in aggregation to a group by on the main fact table datetime ID.  

      For whatever reason it's not hitting the aggregation when I try to use month/year in date time table....

       

      The reason in dax studio is "no column mapping"...


      Any ideas as to why?