Forum Discussion

randyvik's avatar
randyvik
New Member
6 years ago
Solved

Merge vittual tables

Hello All,   I have 2 virtual table variables in the following DAX code: table1 and table2. But I dont know how to merge virtually   Dummy = var table0 = SELECTCOLUMNS(FILTER('Master table','Mast...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    randyvik This however works:

     

    Table 4 = 
    VAR __Table2 = { (1,"A","Black"),(2,"B","White"),(3,"C","Blue")}
    VAR __Table3 = { (1,"A","One"),(2,"B","Two"),(3,"C","Three")}
    VAR A = SELECTCOLUMNS(__Table2,"Key",[Value1],"Color",[Value3])
    VAR B = SELECTCOLUMNS(__Table3,"Key",[Value1],"Number",[Value3])
RETURN
    NATURALINNERJOIN(A, B)

    marcorusso Help! See the Master tables in the attached PBIX. As well as Table 2 and Table 3 and Table 5 (error)

     

randyvik's avatar
randyvik
New Member
6 years ago

Thanks for your quick response. I tried the following code

 

Dummy = var table0 = SELECTCOLUMNS(FILTER('Master table','Master table'[Transacted quantity]<>0),"Date",'Master table'[Date],"Transacted price",'Master table'[Transacted price],"Transacted quantity",'Master table'[Transacted quantity],"Security Name",'Master table'[Security name],"Category",'Master table'[Category],"Sub-Category",'Master table'[Sub-Category])
var table1 = ADDCOLUMNS(table0,"Date rank",RANKX(FILTER(table0,[Security Name]=EARLIER([Security Name])),[Date],,ASC,Dense))
var table2 = SUMMARIZE('Master table','Master table'[Security name],"sold quantity",CALCULATE(SUM('Master table'[Sold quantity])))
var result = NATURALLEFTOUTERJOIN(table1,table2)
return result
 
However I get an error stating that "No common join columns detected. The join function 'NATURALLEFTOUTERJOIN' requires at-least one common join column". How do I specify the join column in a virtual table?
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

randyvik Let me mock this up, NATURALINNERJOIN can be maddening sometimes!