Create a new table from two other tables

[baribir]

Hi All,

I need to make a new table from the contents of two other tables with DAX. And add a column with the time difference(Date1-Date2).

Table 1:

ID1	Date1
1	05-12-18
2	04-12-18
3	04-12-18
4	04-12-18

Table2:

ID2	Date2
1	20-11-18
2	27-11-18
3	04-12-18
4	11-12-18

A new table:

ID1	Date1	ID2	Date2	Diff
1	05-12-18	1	20-11-18	15
1	05-12-18	2	27-11-18	8
1	05-12-18	3	04-12-18	1
1	05-12-18	4	11-12-18	-6
2	04-12-18	1	20-11-18	14
2	04-12-18	2	27-11-18	7
2	04-12-18	3	04-12-18	0
2	04-12-18	4	11-12-18	-7
3	04-12-18	1	20-11-18	14
3	04-12-18	2	27-11-18	7
3	04-12-18	3	04-12-18	0
3	04-12-18	4	11-12-18	-7
4	04-12-18	1	20-11-18	14
4	04-12-18	2	27-11-18	7
4	04-12-18	3	04-12-18	0
4	05-12-18	4	11-12-18	-6

[GilbertQ · Accepted solution]

Hi there

Here is the DAX code

Table = CROSSJOIN('Table1','Table2')

This will give you the result you want

[GilbertQ · Accepted solution]

Here we go

Table =
CALCULATETABLE (
    ADDCOLUMNS (
        CROSSJOIN ( 'Table1', 'Table2' ),
        "DateDiff", DATEDIFF ( 'Table1'[Date1], 'Table2'[Date2], DAY )
    )
)

[Ashish_Mathur · Accepted solution]

Hi,

Just in case you want to do this with M code, try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1TU00jUyMLRQitWJVjICCZmgCBljCpmgCcUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID1 = _t, Date1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID1", Int64.Type}, {"Date1", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table2),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"ID2", "Date2"}, {"ID2", "Date2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"ID2", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type1", {{"Date2", type date}}, "en-IN"),
    #"Added Custom1" = Table.AddColumn(#"Changed Type with Locale", "Custom", each [Date1]-[Date2]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", Int64.Type}})
in
    #"Changed Type2"

You may download my PBI file from here.

Hope this helps.

[baribir]

@Ashish_Mathur,

Thank you!

[Ashish_Mathur]

You are welcome.

[GilbertQ · Accepted solution]

Hi there

Here is the DAX code

Table = CROSSJOIN('Table1','Table2')

This will give you the result you want

[baribir]

Thanks @GilbertQ,

Could you advise how to find the also with Dax add a new column with datediff.  

[GilbertQ]

Hi there

You should be able to do it with a DATEDIFF(StartDate,EndDate,INTERVAL)

[baribir]

@GilbertQ,

At a single DAX expression. I want to use this as a var in other expression to filter by date and datediff.

[GilbertQ · Accepted solution]

Here we go

Table =
CALCULATETABLE (
    ADDCOLUMNS (
        CROSSJOIN ( 'Table1', 'Table2' ),
        "DateDiff", DATEDIFF ( 'Table1'[Date1], 'Table2'[Date2], DAY )
    )
)

[baribir]

@GilbertQ,

Thank you!!!

[GilbertQ]

Pleasusre to assist