Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Build a New table based on other tables

Hi there. After some days I decided to come and ask for help. basically I need to build a new table based on two other tables. I am looking for DAX code to generate the desiered table.

 

I have a Date table

 

Date
1-Jun-22 00:00
2-Jun-22 00:00
3-Jun-22 00:00
4-Jun-22 00:00
5-Jun-22 00:00
6-Jun-22 00:00
7-Jun-22 00:00
8-Jun-22 00:00
9-Jun-22 00:00
10-Jun-22 00:00
11-Jun-22 00:00
12-Jun-22 00:00
13-Jun-22 00:00

 

I also have a Trips Table, where total hours is just the difference between start and end time

 

Name        Start       EndTotal Hours
AES2-Jun-22 20:16   3-Jun-22 13:36    17:20
CEN3-Jun-22 14:36   6-Jun-22 02:57     60:21
ATL3-Jun-22 16:21   6-Jun-22 01:55    57:34
LUM6-Jun-22 03:57   6-Jun-22 21:44    17:47
MER6-Jun-22 02:55   8-Jun-22 11:19    56:24

 

I need to generate the following table, where I summarize the number of hours per day per Name:

 

Date  Name   Hours
1-Jun-22 00:00---00:00
2-Jun-22 00:00AES03:44
3-Jun-22 00:00AES13:36
3-Jun-22 00:00CEN09:24
4-Jun-22 00:00CEN24:00
5-Jun-22 00:00CEN24:00
6-Jun-22 00:00CEN02:57
3-Jun-22 00:00ATL07:39
4-Jun-22 00:00ATL24:00
5-Jun-22 00:00ATL24:00
6-Jun-22 00:00ATL01:55
6-Jun-22 00:00LUM17:47
6-Jun-22 00:00MER21:05
7-Jun-22 00:00MER24:00
8-Jun-22 00:00MER11:19
9-Jun-22 00:00---00:00
10-Jun-22 00:00---00:00
11-Jun-22 00:00---00:00
12-Jun-22 00:00---00:00
13-Jun-22 00:00---00:00

 

I appreciate your help.

DAX
  • Anonymous's avatar
    Anonymous
    4 years ago

    HI Anonymous,

    You can use the following calculated column formula to get the expand result based on crossjoin function and filters:

     

    Expand = 
SELECTCOLUMNS (
    ADDCOLUMNS (
        FILTER (
            CROSSJOIN ( 'Table', 'Calendar' ),
            [Date] >= DATEVALUE ( [Start] )
                && [Date] <= DATEVALUE ( [End] )
        ),
        "Hour",
            VAR diff =
                IF (
                    DATEVALUE ( [Start] ) >= [Date],
                    DATEDIFF ( MAX ( [Start], [Date] ), DATEVALUE ( [Start] ) + 1, MINUTE ),
                    DATEDIFF ( DATEVALUE ( [Date] ), MIN ( [End], [Date] + 1 ), MINUTE )
                )
            RETURN
                INT ( diff / 60 ) & ":"
                    & MOD ( diff, 60 )
    ),
    "Name", [Name],
    "Date", [Date],
    "Hour", [Hour]
)

     

     

    Regards,

    Xiaoxin Sheng

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    Looks like there is a bug in the DAX engine that does not handle certain time values well.

     

    This measure is how it is supposed to work

     

     

    Total hours = 
var n = summarize(Results,Results[Name],Results[Start],Results[End],Results[Date])
var b = ADDCOLUMNS(n,"m",countrows(intersect(GENERATESERIES([Date]*1440,([Date]+1)*1440-1),GENERATESERIES([Start]*1440,[End]*1440-1))))
return format(0+int(sumx(b,[m])/60),"00:")&format(0+mod(sumx(b,[m]),60),"00")

     

     

    But it comes up empty for two events for some bizarre reason.

     

    This calculated column works ok, in contrast.

     

     

     

    Minutes = 
switch (TRUE()
,int([Start])=int([Date]) && int([End])=int([Date]),([End]-[Start])*1440  -- start and end on same day
,[Date]>=[Start] && int([Date])<int([End]),1440 -- full day
,int([Date])=int([Start]),1440-([Start]-int([Start]))*1440 -- first day
,int([Date])=int([End]),([End]-int([End]))*1440  ) -- last day

     

     

     

    See attached for the result.

    NOTE:  Your desired result is unachievable. You need to make compromises by using a matrix, for example.

     

    see attached

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    HI Anonymous,

    You can use the following calculated column formula to get the expand result based on crossjoin function and filters:

     

    Expand = 
SELECTCOLUMNS (
    ADDCOLUMNS (
        FILTER (
            CROSSJOIN ( 'Table', 'Calendar' ),
            [Date] >= DATEVALUE ( [Start] )
                && [Date] <= DATEVALUE ( [End] )
        ),
        "Hour",
            VAR diff =
                IF (
                    DATEVALUE ( [Start] ) >= [Date],
                    DATEDIFF ( MAX ( [Start], [Date] ), DATEVALUE ( [Start] ) + 1, MINUTE ),
                    DATEDIFF ( DATEVALUE ( [Date] ), MIN ( [End], [Date] + 1 ), MINUTE )
                )
            RETURN
                INT ( diff / 60 ) & ":"
                    & MOD ( diff, 60 )
    ),
    "Name", [Name],
    "Date", [Date],
    "Hour", [Hour]
)

     

     

    Regards,

    Xiaoxin Sheng