Combining Tables and Adding Rows

[SweAusBenno]

Hi, 

Fairly new to Power BI and hoping someone can help. 

I am looking for the DAX code in order to create a new table (Table C) from two calculated tables (Table A & Table B).

Essentially, for each row in Table A the result (Table C) has a seperate row for each item, for each period but does not create non-applicable combinations, for example Green - Flags.

Hope that explains what I am looking for, Im supper grateful for any help/thoughts/comments.

Thanks!

[ziyabikram96]

create new table c and then us the following dax

 table = crossjoin(table a, table b)

[ryan_mayu · Accepted solution]

@SweAusBenno 

rename the colors column in table B and use DAX to create a table.

Table = 
VAR tbl=ADDCOLUMNS(CROSSJOIN('Table A',TableB),"check",if('Table A'[Colors]=TableB[Color],"Yes"))
return SELECTCOLUMNS(FILTER(tbl,[check]="Yes"),"period",'Table A'[period],"Colors",'Table A'[Colors],"item",TableB[Item])

[SweAusBenno]

Thanks! That seemed to do the trick 🙂

[ryan_mayu]

you are welcome