Combine tables into one table without clear relationship in DAX

[Spartanos]

I would like to combine the values of two columns into one column, but the tables don't have a link, only the month.

I can't append the tables, because the tables are very different from each other, I just want to append the values per month for both tables. The rows for both tables are not identical, I have remove the blank rows in table source. Next, I have to filter for FB1 in table X.

table 'source'                                         table   'X'                                   Table 'combined'

Month      Source                                   Month     FB1                              Month         Source (average)    FB1  (average)

2022-01    100                                      2022-01   120                              2022-01         100                      120

2022-03    120                                      2022-02   130                              2022-02         120                      130

2022-03     130                                     2022-03   140                              2022-03         130                      140 

2022-04     140                                     2022-04   150                              2022-04         118,75                175

2022-04     110                                     2022-04   200

2022-04     105                                    

2022-04     120

How can I do so in DAX?

Thank you.

[v-yalanwu-msft · Accepted solution]

Hi, @Spartanos ;

Since your filter value "FX1" is a dynamic value, creating a new table is a static value, so measure is recommended.

First create a new table.

combine = UNION(VALUES('Source'[Month]),VALUES('Table_X'[Month]))

Second create two measure.

ave-source = 
IF(ISINSCOPE(combine[Month].[Day]),CALCULATE( AVERAGE('Source'[Price]),FILTER('Source',[Month]=MAX('combine'[Month]))),
IF( ISINSCOPE(combine[Month].[Month]),CALCULATE( AVERAGE('Source'[Price]),FILTER('Source',EOMONTH( [Month],0)=EOMONTH( MAX('combine'[Month]),0))),
AVERAGE('Source'[Price])))

ave-x = 
IF(ISINSCOPE(combine[Month].[Day]),CALCULATE( AVERAGE('Table_X'[Price]),FILTER('Table_X',[Month]=MAX('combine'[Month]))),
IF( ISINSCOPE(combine[Month].[Month]),CALCULATE( AVERAGE('Table_X'[Price]),FILTER('Table_X',EOMONTH( [Month],0)=EOMONTH( MAX('combine'[Month]),0))),
AVERAGE('Table_X'[Price])))

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @Spartanos ;

You could create a new table.

Table = SUMMARIZE('X',[Month],"FBI",AVERAGE(X[FB1]),"Source",CALCULATE(AVERAGE(source[source]),FILTER('source',[Month]=EARLIER(X[Month]))))

The final output is shown below:

Is '02-01-2022' ->'2022-02-01' or '2022-01-02'? and Why is FB?

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SpartaBI]

Hey @Spartanos ,attached the code.
2 points:
1. In case you have all the months in one of the tables, put that table in the left of the  NATURALLEFTOUTERJOIN function.
2. In case your month column will be of date format replace the [  & "" ] with [ + 0 ] (In your picture it looks like text so I did the code for text)

Combined = 
VAR _source = 
SELECTCOLUMNS(
	ADDCOLUMNS(
		VALUES(source[Month]),
		"AvgSource", CALCULATE(AVERAGE(source[Source]))
	),
	"Month", source[Month] & "",
	"AvgSource", [AvgSource]
)

VAR _X = 
SELECTCOLUMNS(
	ADDCOLUMNS(
		VALUES(X[Month]),
		"AvgFB", CALCULATE(AVERAGE(X[FB]))
	),
	"Month", X[Month] & "",
	"AvgFB", [AvgFB]
)
VAR _result = NATURALLEFTOUTERJOIN(_X, _source)

RETURN
	_result
	

[Spartanos]

Many thanks, there is one issue left. I have to place an filter in var_source. In the column (source[ticker]), I have to plance a filter, ="FB01" (source[ticker])="FB01"). I can't figure out how this works, I have tried many options in the DAX code.

[SpartaBI]

@Spartanos 

Combined = 
VAR _source = 
SELECTCOLUMNS(
	ADDCOLUMNS(
		CALCULATETABLE(
			VALUES(source[Month]),
			source[ticker] = "FB01"
		),
		"AvgSource", CALCULATE(AVERAGE(source[Source]))
	),
	"Month", source[Month] & "",
	"AvgSource", [AvgSource]
)

VAR _X = 
SELECTCOLUMNS(
	ADDCOLUMNS(
		VALUES(X[Month]),
		"AvgFB", CALCULATE(AVERAGE(X[FB]))
	),
	"Month", X[Month] & "",
	"AvgFB", [AvgFB]
)
VAR _result = NATURALLEFTOUTERJOIN(_X, _source)

RETURN
	_result
	

[Spartanos]

Please see my pbit file, https://drive.google.com/file/d/1HTrMuiJEDAPhLpaPXYa2A9nooybv3UdT/view?usp=sharing.

The left table should have the same numbers as the other two tables, but that isn't the case. I have placed a filter in the right table, FBX1. For some rows in the source table there are now prices for some days, they should be excluded with respect to the average price, so for table source FBX1, the average price for 2022-01 should be 10+10+10/3=10. For table X it should be 12,33.

[SpartaBI]

Each month has only one row in both tables? Than you can relate them and user realted function to bring the FB column to the other table. 
If from some reason you can't relate them, you can use the lookupvalue function:
https://www.youtube.com/watch?v=XTkrNdU6jWs
Better to just merge them in Power Query before you even load them to the model