topic Re: Date and time in an IoT project in DAX Commands and Tips

Date and time in an IoT project

ppvinsights — Tue, 10 Jan 2023 15:16:56 GMT

Hello Community,

In an IoT project we have some machines which send several times a day messages. So we have a dimension table

DimMachine

and lets say a fact table

FactMessage

The fact table has a timestamp. And several kpis like a charging rate.

An active machine is a machine which sends at least one message in a give time period. So I define

no of active machines = COUNTROWS ( FILTER ( DimMachine, NOT ISBLANK ( COUNTROWS ( RELATEDTABLE ( FactMessage ) ) ) ) )

Because we want to see active machines "in a given period" we define a calendar-dimension DimDate. So we need to extract the date of the timestamp in the FactMessage.

Usually I would introduce a time dimension as well, extract the time as well and delete the timestamp in my fact data. But:

We want to have a line chart in which we can show some of the kpis of the messages. We want to see, if the charging rate was over a given limit - we say "more than three time over the limit" then we have a problem. So we cannot show an average, min/max. We need to see the raw value.

If using DimDate and DimTime for the x-axis in a line chart, we introduce (for the customer) a strange drillthrough. It looks much nicer if I would use the timestamp of the FactMessage for the x-Axis, because so I have a continouus axis which is much nicer than the default drill-down axis (if using DimDate and DimTime).

But using the Timestamp I would use data from a fact table in an axis - which I would love to avoid (e.g.: if having another FactTable with say "errors" I could not bring this information in the same visual - because the timestamp of FactMEssage is not a shared dimension).

But introducing a combined Date/Time Dimension with 86.400 rows per day cannot be the solution for that.

Other pitfall if working with DimDate AND DimTime are:

when we have to show the raw data in a table, you alway have two columns: date and time - and you have to sort both. Using the timestamp you only need to sort one column.
If you have any date/time calculation (e.g. "show me the last message before myself"), you first have to add date and time again, before you can calculate anything

Do you understand what I mean? How do you handle those situations?

Thanks

Holger

Re: Date and time in an IoT project

tamerj1 — Tue, 10 Jan 2023 17:03:16 GMT

Hi ppvinsights

I'm trying to understand. Need some screenshots of your report or at least the shape of your report aling with the expected results. Cannot picture what are you trying to achieve.
one possibility is to have extra two columns, one for date only connected to dim date and one for time only connected to dim time. for any calculation that requires the full datetime value you can allways use the original column from the fact table.

Re: Date and time in an IoT project

ppvinsights — Thu, 12 Jan 2023 07:04:46 GMT

Hi,

yeah - it is hard to explain. The essence (after two days it is easier to tell something about the real problem 😉 ) is, that I need the timestamp in my axis.

==> I want to show testdata from a machine - like how much electricity was used when starting something. My customer want to see ALL results in a line chart. If I would use DimDate, the line chart would show a maximum or mean of my testdata. Even if I use DimDate AND DimTime as an axis, my customer would have to drill down to see every result without any aggregation. So I use the timestamp of the fact table because I do not have a "DimDateTime" Dimension. But: What if I need another kpi in the same line chart from another fact table? Because the timestamp is not a shared dimension...?

So I fully understand why you have to separate date and time in a filtertable/shared dimension. But what, if I need to have timestamps in my axis?

Is there any cool idea I do not see?

Holger

Re: Date and time in an IoT project

tamerj1 — Thu, 12 Jan 2023 07:29:31 GMT

ppvinsights
You may try using TREATAS to change the lineage of the Fact.[DateTime] . Simething like

CALCULATE (
[Measure],
TREATAS ( VALUES ( Fact.[DateTime] ), Fact.2[DateTime] )
)

Re: Date and time in an IoT project

ppvinsights — Thu, 12 Jan 2023 07:38:18 GMT

Yeah, I understand - but works only, if the timestamp in Fact 1 is exactly the same as in Fact 2. I think I have a phantom problem and there is no obvious solution for a shared dimension with DateTimes...

Re: Date and time in an IoT project

tamerj1 — Thu, 12 Jan 2023 08:25:59 GMT

ppvinsights
perhaps no need to have an extremely huge size DateTime table, we only need a DateTime table that contains the exiating datetimes in our two fact tables.

DateTime = DISTINCT ( UNION ( ALLNOBLANKROW ( Fact.1[DateTime] ), ALLNOBLANKROW ( Fact.2[DateTime] ) ) )

Re: Date and time in an IoT project

ppvinsights — Thu, 12 Jan 2023 10:03:00 GMT

Yes, your are totally right! Because really often we just talk about <10 DataPoints per day. So I could introduce a DimDate, a DimTime and between the tose and the fact table I just add this TimeStamp-Union Table with two calulated "Date" and "Time" cols.

Thanks for your time!