how to do to merge 2 Differents tables with multi filters ?

[Anonymous]

hi,

J tried to use the function DAX  in using 2 differents tables.

it doesn't work

Table A as incident

Number             Age_Diff

inc000000            2

inc000001          50 

Table B  as Table_Parm_Age

Code 1   Code 2   Min   Max        Short Desc 

Age          1             0        3        1 - from 0 to 3 days

Age          2            2        7          2 - from 2 to 7 days

Age          3            6      60          3 - from 6 to  60  days

Age          4            59    999999   4 - more than 59 days 

My goal is to merge these 2 tables to One table.

Result  : Table_join_inc_parm = FILTER(CROSSJOIN(incident;Table_Parm_Age); (incident[Age_Diff - Entier] >= Table_Parm_Age[Min] && incident[Age_Diff - Entier] < Table_Parm_Age[Max] ) ).  

it doesn'work because, count of table A ( incident) < count final ( Table_join_inc_parm)  .

I would like that 2 counts are equal.

 I tried an another method 

Table 2 = GENERATEALL ('incident';VAR Table1Age = 'incident'[Age_Diff]RETURNSELECTCOLUMNS (CALCULATETABLE ( 'Table_Parm_Age'; 'Table_Parm_Age'[Code1] ="Age" && 'Table_Parm_Age'[Min] >= 'incident'[Age_Diff - Entier] && Table1Age < 'Table_Parm_Age'[Max] );"Desc"; 'Table_Parm_Age'[Short Desc])) 

Here, all columns of incidents and ONE column of Table_Parm_Age as Short Desc   are installed in a result table as TABLE2.

 It doesn't work too. 

Then How to do it to correct a good result for the SAME count between table incident and  result Table_join_inc_parm as Table 2 or Table 

Thank you in advance to help me .

 sincerely

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

After testing, your first formula works fine. I think maybe you created a column with the formula rather than a table.  We need to create a table while using CROSSJOIN function. I created a sample in two ways, DAX and M language. You could reference to have a try.

• DAX

Table =
FILTER (
    CROSSJOIN ( incident, Table_Parm_Age ),
    ( incident[Age_Diff] >= Table_Parm_Age[Min]
        && incident[Age_Diff] < Table_Parm_Age[Max] )
)

 

• M

= let diff = [Age_Diff] in Table.SelectRows(#"Table_Parm_Age (2)", each diff <= [Max] and diff >= [Min])

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WysxLNgADJR0lI6VYHYSIIVDE1EApNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Number = _t, Age_Diff = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", type text}, {"Age_Diff", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let diff = [Age_Diff] in Table.SelectRows(#"Table_Parm_Age (2)", each diff <= [Max] and diff >= [Min])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Code1 1", "Code1 2", "Min", "Max", "Short Desc"}, {"Custom.Code1 1", "Custom.Code1 2", "Custom.Min", "Custom.Max", "Custom.Short Desc"})
in
    #"Expanded Custom"

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

After testing, your first formula works fine. I think maybe you created a column with the formula rather than a table.  We need to create a table while using CROSSJOIN function. I created a sample in two ways, DAX and M language. You could reference to have a try.

• DAX

Table =
FILTER (
    CROSSJOIN ( incident, Table_Parm_Age ),
    ( incident[Age_Diff] >= Table_Parm_Age[Min]
        && incident[Age_Diff] < Table_Parm_Age[Max] )
)

 

• M

= let diff = [Age_Diff] in Table.SelectRows(#"Table_Parm_Age (2)", each diff <= [Max] and diff >= [Min])

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WysxLNgADJR0lI6VYHYSIIVDE1EApNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Number = _t, Age_Diff = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", type text}, {"Age_Diff", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let diff = [Age_Diff] in Table.SelectRows(#"Table_Parm_Age (2)", each diff <= [Max] and diff >= [Min])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Code1 1", "Code1 2", "Min", "Max", "Short Desc"}, {"Custom.Code1 1", "Custom.Code1 2", "Custom.Min", "Custom.Max", "Custom.Short Desc"})
in
    #"Expanded Custom"

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

hi,

Thank you for your answer.  in a first option, it works . 

In fact, I discovered the error on the operation ">". I corrected it ">=" instead of this on !. The result for 2 different tables are identical ! . It s my fault because I am too tired during my research thoroughly !...

For 2nd option, I try to understand your approach. Because, I am still a beginner on th Power BI !....

Once again thank you for your help !

Sincerely