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','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])))return ??? Output for table1 DateTransacted priceTransacted quantitySecurity NameCategorySub-CategoryDate rank10-01-2017 00:0030100MiraeMutual FundEquity110-02-2017 00:0030100MiraeMutual FundEquity210-03-2017 00:0030100MiraeMutual FundEquity310-04-2017 00:0030100MiraeMutual FundEquity410-05-2017 00:0030100MiraeMutual FundEquity515-05-2017 00:0030100MiraeMutual FundEquity622-05-2017 00:0030100MiraeMutual FundEquity712-06-2017 00:0030100MiraeMutual FundEquity810-07-2017 00:0030100MiraeMutual FundEquity910-08-2017 00:0040200MiraeMutual FundEquity1017-04-2018 00:0040200HDFC BankStocksStocks117-04-2018 00:0040200KEIStocksStocks117-04-2018 00:0040200RelianceStocksStocks112-03-2019 00:0040200SundaramMutual FundEquity120-05-2019 00:0040200Asian paintsStocksStocks120-05-2019 00:0040200HDFCStocksStocks120-05-2019 00:0040200PidiliteStocksStocks123-10-2019 00:0040200Kotak MahindraStocksStocks119-12-2019 00:0050300Axis bluechipMutual FundEquity123-12-2019 00:0050300HULStocksStocks127-02-2020 00:0050300HDFCStocksStocks227-02-2020 00:0050300HDFC BankStocksStocks202-03-2020 00:0050300HULStocksStocks202-03-2020 00:0050300RelianceStocksStocks207-04-2020 00:0050300TitanStocksStocks109-04-2020 00:0050300TCSStocksStocks109-07-2020 00:0050300Axis short termMutual FundDebt1 Output for table2Security namesold quantityAsian paints0HDFC0HUL0Reliance5Axis short term0TCS0KEI0Titan0Sensex0HDFC Bank0Kotak Mahindra0Mirae0Sundaram0Pidilite0Axis bluechip0 In the return statement of DAX, I want a code to merge these tables virtually using their security name column respectively. I cannot create a seperate table for this operation due to other constraints. Also I'm new to Power BI, so it will be of great help to me if anyone of you could share me your solution for this Thanks

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','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])))
return ???

Output for table1

Date	Transacted price	Transacted quantity	Security Name	Category	Sub-Category	Date rank
10-01-2017 00:00	30	100	Mirae	Mutual Fund	Equity	1
10-02-2017 00:00	30	100	Mirae	Mutual Fund	Equity	2
10-03-2017 00:00	30	100	Mirae	Mutual Fund	Equity	3
10-04-2017 00:00	30	100	Mirae	Mutual Fund	Equity	4
10-05-2017 00:00	30	100	Mirae	Mutual Fund	Equity	5
15-05-2017 00:00	30	100	Mirae	Mutual Fund	Equity	6
22-05-2017 00:00	30	100	Mirae	Mutual Fund	Equity	7
12-06-2017 00:00	30	100	Mirae	Mutual Fund	Equity	8
10-07-2017 00:00	30	100	Mirae	Mutual Fund	Equity	9
10-08-2017 00:00	40	200	Mirae	Mutual Fund	Equity	10
17-04-2018 00:00	40	200	HDFC Bank	Stocks	Stocks	1
17-04-2018 00:00	40	200	KEI	Stocks	Stocks	1
17-04-2018 00:00	40	200	Reliance	Stocks	Stocks	1
12-03-2019 00:00	40	200	Sundaram	Mutual Fund	Equity	1
20-05-2019 00:00	40	200	Asian paints	Stocks	Stocks	1
20-05-2019 00:00	40	200	HDFC	Stocks	Stocks	1
20-05-2019 00:00	40	200	Pidilite	Stocks	Stocks	1
23-10-2019 00:00	40	200	Kotak Mahindra	Stocks	Stocks	1
19-12-2019 00:00	50	300	Axis bluechip	Mutual Fund	Equity	1
23-12-2019 00:00	50	300	HUL	Stocks	Stocks	1
27-02-2020 00:00	50	300	HDFC	Stocks	Stocks	2
27-02-2020 00:00	50	300	HDFC Bank	Stocks	Stocks	2
02-03-2020 00:00	50	300	HUL	Stocks	Stocks	2
02-03-2020 00:00	50	300	Reliance	Stocks	Stocks	2
07-04-2020 00:00	50	300	Titan	Stocks	Stocks	1
09-04-2020 00:00	50	300	TCS	Stocks	Stocks	1
09-07-2020 00:00	50	300	Axis short term	Mutual Fund	Debt	1

Output for table2

Security name	sold quantity
Asian paints	0
HDFC	0
HUL	0
Reliance	5
Axis short term	0
TCS	0
KEI	0
Titan	0
Sensex	0
HDFC Bank	0
Kotak Mahindra	0
Mirae	0
Sundaram	0
Pidilite	0
Axis bluechip	0

In the return statement of DAX, I want a code to merge these tables virtually using their security name column respectively. I cannot create a seperate table for this operation due to other constraints. Also I'm new to Power BI, so it will be of great help to me if anyone of you could share me your solution for this

Thanks

Need Help

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)

working68.pbix89 KB