Create a single table many to many relationship using DAX

[ArvindJha]

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

[olgad]

@ArvindJha 

FILTER(
CROSSJOIN('Visits', 'Height'),
'Visits'[PERSON] = 'Height'[PERSON]
)

[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

[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]

The name and datatype both are same , the 1st solution of CROSSJOIN works the only challenge is i have 30+columns and i need to enter 30 rows for that , is there any alternative to writing 30 rows?

[Kedar_Pande]

@ArvindJha 

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]

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]
)

[ArvindJha]

Hello 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