Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello Community,
In an IoT project we have some machines which send several times a day messages. So we have a dimension table
and lets say a fact table
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:
Do you understand what I mean? How do you handle those situations?
Thanks
Holger
Solved! Go to Solution.
@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] ) )
)
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
@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] )
)
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...
@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] ) )
)
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!
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
9 |
User | Count |
---|---|
25 | |
22 | |
12 | |
11 | |
10 |