[ Joining 2 Tables with specif layout ]

[MarceloVieira]

Hello everyone, im trying to do find a better solution, than using cross join with all the context, any help is appreciated!!! All the columns on the 2 fact table, have dimensions with 1 to many relationships, connected to them.

ajuda power bi.xlsx

[ryan_mayu]

@MarceloVieira 

pls see the attachment below below

however, the question is how to get the data highlighted in yellow? could you pls elaborate more ?

[MarceloVieira]

@ryan_mayu thks for the pbix file!!

Its almost there, but i cant loose the data in yellow in the merge/append process. I need the full outter join (which you did), but i cant loose the [month data] / [vision] data in the process. Those 2 columns need to be repeated.

[month data] contains near 700 [month bill acc] and every time i have a context existent in table 2 and no existent in table 1, i need to add that row for that [month bill acc] in table 1. The original table 1, have some years of [month data], each of them have repeated [month bill acc], and my difficult here is the sometimes table 2, has data not contained in table 1.

[ryan_mayu]

pls provide the logic of getting the data. you must have different months in your real data. Then how can we know to display 202401 or 202402 or other month?

[MarceloVieira]

Hi ryan, i tried to make a better example.  (i cant publish the excel right now because im working and it will be blocked).

The vision column (days) are a count related to [month bill acc] minus [month data], and if the difference is zero then 30d, if it is 1, then 60d, if its 2 months then 90d, and so on. To do that, we use a sequential yearmonth formula (year * 12 + month - 1). 

[month data] will never be greater thand [month bill acc] in this model, but it can be lower.

[ryan_mayu]

@MarceloVieira 

14770 is from locaion b and type n . how can we link that to location g and type n?

pls also paste the table here like below

month datamonth bill acclocationversiontypetp clientcityvalor billing

202401	202401	c	30d	n	high	no info	322
202401	202401	d	30d	n	high	no info	336
202401	202401	a	30d	n	index	no info	210
202401	202401	b	30d	n	low	no info	311
202401	202401	b	30d	n	low	no info	111
202311	202401	c	90d	f	high	no info	49
202311	202401	d	90d	f	high	no info	46
202311	202401	a	90d	f	low	no info	32
202311	202401	b	90d	n	low	no info	14
202311	202401	b	90d	n	low	no info	555
202311	202401	xa	90d	n	high	no info	26
202311	202401	xb	90d	n	index	no info	646
202311	202401	e	90d	n	index	no info	28
202311	202401	f	90d	f	high	no info	22
202311	202401	g	90d	g	index	no info	23

that will be easier for us to import data.

[MarceloVieira]

@ryan_mayusorry about the picture, i couldnt upload the file, because of the work protections.

File update here: ajuda power bi.xlsx

My model above was with a error, and then sorrrryyyyy again, because replicanting the model in excel by hand is being a challenge task in itself.

I hope now this model is without any errors.

Table1 columns should repeat itself in table 3, nothing should be different for table1, except for the addition of the column "valor liquid".

Table 2 should have "newer" rows created in table 3, only for those rows that are not present in table 1.

[ryan_mayu · Accepted solution]

the solution maybe only work for this scenario. If we have more dates, e.g. 202402, then will not work. Let's see if anyone else can help you on this.

[MarceloVieira]

Thks for the help, i did 2 models, all in dax.

1 of them i did 3 crossjoin with helper tables that contained full context of the join columns in table 1 and table 2. The crossjoin were done with table 1, and on a 2nd moment i joined the "valor  liquid" column, on the full crossjoined table 3 (this was a heavy taks, lots of empty rolls in the end).

The 2nd model i kept the table 1 and table 2 separed, and created all the dimensions i needed to have on the dashboard (1 to many relatioship from dimensions to facts), connecting to table 1/2. It worked much better for what was being asked, just needed some calculations on measures filtering visible yearmonths on table 1 for the visual.