Forum Discussion

JRive116's avatar
JRive116
Regular Visitor
11 months ago
Solved

Data Granularity

Hi everybody! I'm a rookie in PBi and need some help. I'm pulling 2 folders. Folder "A" has excel tables with minute increment flow measurement data for 12 months. Folder "B" has excel tables wit...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    10 months ago

    JRive116 I split your TableA date/time into a date and time columns. I then created a third table of distinct time values at the 10 minute increment level by doing this:

    Time Table = DISTINCT( 'TableA'[DATE_TIME "A".2] )

    Relate this to both of your tables to the time columns in each.

    Plot it like this:

     

     

     

AmiraBedh's avatar
AmiraBedh
Super User
11 months ago

Hello !

Thank you for posting on MS Fabric community.

Keep 2 separate tables and both should be pointing to a unique minute level time table so you need to build a time table at minute grain for your 12 months 

Time =
VAR Start = MINX(ALL(FactMinute), FactMinute[DateTime])
VAR End   = MAXX(ALL(FactMinute), FactMinute[DateTime])
RETURN
ADDCOLUMNS(
    CALENDAR(Start, End),
    "MinuteStamp",
     [Date]
)

and in both fact tables you need to have a key column that matches the time table minute stamp.

For the minute fact you can use the minute date time and for the hourly fact you can create HourStart = DATETIME(Date, Hour, 0, 0) so it matches the time table at the exact hour.

Your relationships should be :

  • Time[DateTime] (unique) : one-to-many to MinuteFact[DateTime]

  • Time[DateTime] (unique) : one-to-many to HourFact[HourStart]

Both relationships can be active.

Then create your measures.