Forum Discussion
Helper IIICreate a single table many to many relationship using DAX
Hi all,
I want to create single table out of two tables with many to many relationship using DAX , LOOKUP and RELATED does not work.
For time being i have created relationship between them but it does not solve all my problems
| PERSON | VISIT | WEIGHT |
| A | Visit1 | 100 |
| A | Visit2 | 90 |
| A | Visit3 | 80 |
| B | Visit1 | 80 |
| B | Visit2 | 70 |
| B | Visit3 | 60 |
| PERSON | HEIGHT |
| A | 140 |
| A | 139 |
| B | 142 |
Thanks,
Arvind
12 Replies
- olgad
Resident Rockstar
Cant you just merge them in the Power Query? full outer join?
Or alternatevely,CombinedTable =
SELECTCOLUMNS(
FILTER(
CROSSJOIN('Visits', 'Height'),
'Visits'[PERSON] = 'Height'[PERSON]
),
"PERSON", 'Visits'[PERSON],
"VISIT", 'Visits'[VISIT],
"WEIGHT", 'Visits'[WEIGHT],
"HEIGHT", 'Height'[HEIGHT]
)
ArvindJhaHello Olgad , it works i needed one input , here i just shared sample data with only 4 columns but in reality i have 20+ columns , so do i need to write 20 lines (one for each column) or is there a way it takes all columns automatically using * or something that would be helphul in such scenarios
- ArvindJha
Hello Olgad , thanks for the response also to your question on join why i am not taking join is that there are some transformations which is making m-query very slow so i doing them in DAX which is fast and then unable to move back to m-query as those newly created tables using DAX are not visible in m-query
- Kedar_Pande
Super User
Create a New Table
CombinedTable =
SUMMARIZE(
'VISIT_TABLE',
'VISIT_TABLE'[PERSON],
'VISIT_TABLE'[VISIT],
'VISIT_TABLE'[WEIGHT],
"HEIGHT",
CALCULATE(
MAX('HEIGHT_TABLE'[HEIGHT]),
TREATAS(
VALUES('VISIT_TABLE'[PERSON]),
'HEIGHT_TABLE'[PERSON]
)
)
)💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn- ArvindJha
Thanks for the response Kedar , output is as below , it takes only the max height 140 and ignores 139 for Person A which is not the expected outcome
- olgad
Try this, but this is inner join
CombinedTable =
NATURALINNERJOIN('Visits', 'Height')- ArvindJha
Hi Olgad , thanks for response , it doesn't work
- olgad
Person shall be the common column, check if it is the same datatype sometimes when one is string/text and the other is 'any' or different spelling person Person or there is a space in the name of one then it doesnt work
- ArvindJha
Hi Olgad , thanks for the response , it gives the below message
The Column with the name of 'PERSON' already exists in the 'Table' Table.
It is because PERSON is present in both tables?
- olgad
Yes, please rename it first and check if it works, are there any other columns with the same name in both tables? In this case they will give you the same problem.
When we do selectcolumns, we specifically say which columns to take, but when not, there cannot be the columns with the same name. In Power Query such columns will automatically get Person.1 name
