Forum Discussion
[ Joining 2 Tables with specif layout ]
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.
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.
Super UserAnonymous
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.
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
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.
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.