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]
);
  • 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 at 15:34
  • What tool are you using to build your model? – Mr.Brownstone Jun 5 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 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 – Mr.Brownstone Jun 5 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 at 20:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.