Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

creating key for time table

hi,

as an intro, i'm trying to study the no. of runway movements [arrival (on block), departure (off-block), takeoffs (wheels-up) & touchdowns (wheels-down)] for a particular aiport. i have created a date-table & also a time-table as suggested in a youtube linked here. that video suggested that the timekeys be created & linked in the fact table & the time-table. 


however, as mentioned earler, i have 4 types of info that i need to measure i.e.

1a. planned arrival (on block) time
1b. actual arrival (on block) time

2a. planned departure (off-block) time
2b. actual departure (off-block) time

3. takeoff (wheels-up) time
4. touchdown (wheels-down) time

i tried to create a timekey that's pegged to 1 of those info (i.e. planned departure time). it worked in calculating the total no. of planned departures (using counta dax function).

it wouldn't, however, work to calculate for the totals of the other types of info (i.e. for items 1b, 2a, 2b, 3, & 4 listed above). i suspect that it's because the data model is based on linking 1 key in fact table (using planned departure time column) & another key in the time-table.

 

i'd appreciate urgent help to resolve this. 


the sample data can be found here

 

tks & krgds, -nik

5 ACCEPTED SOLUTIONS
jdbuchanan71
Super User
Super User

@Anonymous 

You can create a relationship between your time table and each of the time fields in your table.

jdbuchanan71_0-1618647718375.png

5 of them will be inactive but we turn them on using meausre.

I made a basic measure that just counts the flights.

Flight Count = COUNTROWS ( Flights )

Then the first flights measure is just there so the name is the same format as the others.

Flights PLN_DEP_TIM = [Flight Count]

The active relationship betwen time and flights is on the PLN_DEP_TIM field so this one just works.

For the rest we do them like this.

Flights ACT_DEP_TIM = CALCULATE ( [Flight Count], USERELATIONSHIP ( Flights[ACT_DEP_TIM], time[Time] ) )

And you make one of these for each of the additional time columns, then we can make a visual like this.

jdbuchanan71_1-1618647944300.png

The counts on the blank Quartile row are those records that did not have a time in that column.  I have attached my sample book for you to look at.

 

View solution in original post

I just did that in excel then linked my .pbix to the .xls file to pull in the time table but you can generate it using DAX as a new table with this.

Times = 
VAR _Hour = SELECTCOLUMNS ( GENERATESERIES ( 0, 23 ), "Hour", [Value] )
VAR _Min = SELECTCOLUMNS ( GENERATESERIES ( 0, 59, 1 ), "Minute", [Value] )
VAR _Second = SELECTCOLUMNS ( GENERATESERIES ( 0, 0, 1 ), "Second", [Value] )
VAR _Times = ADDCOLUMNS ( CROSSJOIN ( _Hour, _Min, _Second ), "Time", TIME ( [Hour], [Minute], [Second] ) )
RETURN
    ADDCOLUMNS (
        _Times,
        "Quartile",
            SWITCH (
                TRUE (),
                [Hour] < 6, "12 AM - 6 AM",
                [Hour] < 12, "6 AM - 12 PM",
                [Hour] < 18, "12 PM - 6 PM",
                "6 PM - 12 AM"
            )
    )

View solution in original post

Anonymous
Not applicable

@jdbuchanan71 

hi again @jdbuchanan71 ,

 

is it possible to add another column to the table using the same dax for hourly quartile using SWITCH as follows

"HourlyQuartile",
SWITCH(
TRUE(),
[Minute] < 15, "0-14",
[Minute] < 30, "15-29",
[Minute] < 45, "30-44",
"45-59"

tks & krgds, -nik

View solution in original post

@Anonymous ,

Just add it as a calcualated column on your existing time table.

jdbuchanan71_0-1636510138898.png

 

View solution in original post

Anonymous
Not applicable

haha... that e-z !
many tks again, @jdbuchanan71.
krgds, -nik

View solution in original post

10 REPLIES 10
CTozzi
Resolver I
Resolver I

@Anonymous what if you simply unpivot the columns? You will then have one row for each event and one single relationship.

Anonymous
Not applicable

hi @CTozzi,

the ori data table that m working on actually contains >26 mil rows of data. i don't think i want to take that path. the data sample (around 418 rows) i made for this was an extraction fm that ori data table.

 

tks fr the suggestion tho,

krgds: -nik

@Anonymous I understand. Anyway, that wouldn't be a problem for power BI. Give it a try if you want to simplify.

jdbuchanan71
Super User
Super User

@Anonymous 

You can create a relationship between your time table and each of the time fields in your table.

jdbuchanan71_0-1618647718375.png

5 of them will be inactive but we turn them on using meausre.

I made a basic measure that just counts the flights.

Flight Count = COUNTROWS ( Flights )

Then the first flights measure is just there so the name is the same format as the others.

Flights PLN_DEP_TIM = [Flight Count]

The active relationship betwen time and flights is on the PLN_DEP_TIM field so this one just works.

For the rest we do them like this.

Flights ACT_DEP_TIM = CALCULATE ( [Flight Count], USERELATIONSHIP ( Flights[ACT_DEP_TIM], time[Time] ) )

And you make one of these for each of the additional time columns, then we can make a visual like this.

jdbuchanan71_1-1618647944300.png

The counts on the blank Quartile row are those records that did not have a time in that column.  I have attached my sample book for you to look at.

 

Anonymous
Not applicable

many tks @jdbuchanan71.

 

can u kindly advise me on how u created the time table with the quartile info? i'd like to try that in my pbix too.

krgds, -nik

I just did that in excel then linked my .pbix to the .xls file to pull in the time table but you can generate it using DAX as a new table with this.

Times = 
VAR _Hour = SELECTCOLUMNS ( GENERATESERIES ( 0, 23 ), "Hour", [Value] )
VAR _Min = SELECTCOLUMNS ( GENERATESERIES ( 0, 59, 1 ), "Minute", [Value] )
VAR _Second = SELECTCOLUMNS ( GENERATESERIES ( 0, 0, 1 ), "Second", [Value] )
VAR _Times = ADDCOLUMNS ( CROSSJOIN ( _Hour, _Min, _Second ), "Time", TIME ( [Hour], [Minute], [Second] ) )
RETURN
    ADDCOLUMNS (
        _Times,
        "Quartile",
            SWITCH (
                TRUE (),
                [Hour] < 6, "12 AM - 6 AM",
                [Hour] < 12, "6 AM - 12 PM",
                [Hour] < 18, "12 PM - 6 PM",
                "6 PM - 12 AM"
            )
    )
Anonymous
Not applicable

@jdbuchanan71 

hi again @jdbuchanan71 ,

 

is it possible to add another column to the table using the same dax for hourly quartile using SWITCH as follows

"HourlyQuartile",
SWITCH(
TRUE(),
[Minute] < 15, "0-14",
[Minute] < 30, "15-29",
[Minute] < 45, "30-44",
"45-59"

tks & krgds, -nik

@Anonymous ,

Just add it as a calcualated column on your existing time table.

jdbuchanan71_0-1636510138898.png

 

Anonymous
Not applicable

haha... that e-z !
many tks again, @jdbuchanan71.
krgds, -nik

Anonymous
Not applicable

many tks again, @jdbuchanan71.

krgds, -nik

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.