2

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

1

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]
);
5
  • Thank you very much. Happy to hear that it is possible. However, i am getting a Syntax Error on the EVALUATE on your code above. Could you possibly please format it for DAX to be used inside my model, please. Thank you.
    – WadeH
    Jun 5 '19 at 15:34
  • What tool are you using to build your model? Jun 5 '19 at 16:22
  • Im using SSDT in Visual Studio 2015. I've somewhat figured out and gotten a basic understanding of your query but i still cannot get it to run in SSDT. It runs in Dax Studio funnily enough. Its almost like its an end tool query and not a query to create a measure off of? Sorry, DAX beginner her. Thank you
    – WadeH
    Jun 5 '19 at 16:38
  • I've updated my answer for you, once you have included the measure within your model, you should be able to see it within Power BI Jun 5 '19 at 20:08
  • 1
    Thank you very much. Got it figured out. There was an issue in my DateTime relationship, and once that was fixed, your queries worked like a charm.
    – WadeH
    Jun 5 '19 at 20:36

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .