I am in the process of building my first SSAS Tabular model and thought everything was going well, until trying to create a measure by DateTime.
In my warehouse, I have a DIm_Time dimension, which has a DateTime column, with a row for every 5 minutes for the last 2 years. 5 Minutes is the granularity that we require. I addition, there is a TimeID Identity column on the table. IN my ETL, I assign a Time ID to each fact table depending on what 5-minute range it slots into. So the end result, is a relationship between the 2 tables on the ID, with let's say 10 facts per TimeID.
Now I am trying to do a simple count of rows per time range. For example, how many facts for the current hour. The problem I'm finding is that I am seeing no data in either PowerBI or Excel when testing my measure, and I'm 90% sure it's related to the fact that my model is filtering my DateTime as a date when calculating the measure.
Fcts by Date:= CALCULATE ( COUNTA(Fct_Table[IDColumn]), Dim_Time[DateTime])
Please, can someone help point me in the right direction as I am struggling to find anything with regards to working with a DateTime dimension. Thank you very much.
I am, using SQL/SSAS 2017