union of two tables at run time

[Anonymous]

Hello everyone!!

i just wanted to know can we take union of two or multiple tables at runtime in a measure

if yes then how?
also if that is not possible than how can i solve a scenerio in which i want to get real time data  based on the date selected by user but i cannot do that because dynamic m query can only be supported on the data that is fetched using direct query and direct query feature is not supported with mysql.

kindly help!!

[v-lionel-msft]

Hi @Anonymous ,

"i just wanted to know can we take union of two or multiple tables at runtime in a measure"
NO, it's impossible. Measure can only return a dynamic value, not a dynamic table.

For how to use "Direct Query" mode to connect to mysql, you can refer to this article.

DirectQuery DataDirect ODBC drivers in Power BI 

Best regards,
Lionel Chen

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

[amitchandak]

@Anonymous , Actually you can do using union , may be just add measures from two tables.

Like I have table Table A and B joined to C(Dimension)  and have column sales, this will union all data

New Sales = Sum(A[Sales]) + sum(B[Sales])

Measure =

sumx(union(a,b), [sales])

You can also union specific columns.

what is the formula required ?

[Anonymous]

@amitchandak  i want union of two table based on selected month from user i.e.

all outstanding test =

var jan = if ( 1 in values('Date dimension'[Month number]), 'outstanding January'[outstanding jan] )

var feb = if ( 2 in values('Date dimension'[Month number]) , 'outstanding February'[outstanding Feb] )

var mar = if ( 3 in values('Date dimension'[Month number]) , 'outstanding march'[outstanding march] )

var apr = if ( 4 in values('Date dimension'[Month number]) , 'outstanding April'[outstanding April] )

var may = if ( 5 in values('Date dimension'[Month number]) , 'outstanding May'[outstanding may] )

var jun = if ( 6 in values('Date dimension'[Month number]) , 'outstanding June'[outstanding june] )

var july = if ( 7 in values('Date dimension'[Month number]) , 'outstanding July'[outstanding july] )

var aug = if ( 8 in values('Date dimension'[Month number]) , 'outstanding August'[outstanding aug] )

var sep = if ( 9 in values('Date dimension'[Month number]) , 'outstanding September'[outstanding sep] )

var oct = if ( 10 in values('Date dimension'[Month number]) , 'outstanding October'[outstanding oct] )

var nov = if ( 11 in values('Date dimension'[Month number]) , 'outstanding November'[outstanding nov] )

var dec = if ( 12 in values('Date dimension'[Month number]) , 'outstanding December'[outstanding dec] )

return

UNION(jan,feb,mar,---,dec)
something like this
the main idea behind this chose only count from selected months and ignore other in run time

[littlemojopuppy]

Can you provide some sample data to work with?