Sql-server – SSAS Tabular model Date Dimension with Time possible

sql-server-2017ssastabular-model

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

Best Answer

Generally I keep Date and Time in separate dimensions as this reduces the overall cardinality but even with your date dimension having a time element, you should not have a problem obtaining this value. The example below was written on my own system and tested in DAX Studio: 

DEFINE 
    MEASURE Sales[TransactionCountPerTimePeriod] = COUNTROWS('Sales')
EVALUATE
SUMMARIZECOLUMNS (
    'Date'[DateKey],
    "Transaction Count", [TransactionCountPerTimePeriod]
);

In order to get this work within a model, just take the definition of the measure and include it, like so:

TransactionCountPerTimePeriod:=COUNTROWS('Sales')

Then, after publishing your model you should see it show up in DAX Studio, Power BI, Excel or any other reporting tool:

-- no need to define the measure if it is already in the model
-- just use it
EVALUATE
SUMMARIZECOLUMNS (
    'Date'[DateKey],
    "Transaction Count", [TransactionCountPerTimePeriod]
);
Related Question