Creating Relationship between 2 Different Datasets

[syeena]

Let's say I have 2 existing Datasets, one is AnimalInfo and another is Owner. In the Owner dataset (xls. format) consist of 3 sheets aka 3 owners. In the visualization, for instance I have slicer that is able to choose the animal from AnimalInfo set. Then I have a table where the Animal is displayed when chosen from the slicer, as well as display the owner's name indicating that the owner has the Animal chosen, as a Pet. How do I achieve this?

Examples of the desired output when choosing "Cat" in the slicer:

Animal	Ara	Ken	Eva
Cat	Cat (Wendy)	n/a	Cat (Loli)

Another example when choosing "Fish" in the slicer:

Animal	Ara	Ken	Eva
Fish	n/a	Fish (Joji) Fish (Poppy)	n/a

AnimalInfo Dataset

Animal	Colour
Cat	White
Dog	Brown
Fish	Orange

Owner Datasets

Ara (sheet1)

Name of the Pet	Pet
Ben	Dog
Wendy	Cat

Ken (sheet2)

Name of The Pet	Pet
Joji	Fish
Poppy	Hen

Eva (sheet3)

Name of The Pet	Pet
Loli	Cat

[rajendraongole1 · Accepted solution]

Hi @syeena - you can append these three sheets (Ara,Ken and Eva) into a single table named as Owner

Rename columns appropriately for consistency (e.g., Name of the Pet to Pet Name, Pet to Animal).

create a relationship between owner table and Animal tables as lik below

create a new table as 

OwnerMapping =
DATATABLE (
"Owner", STRING,
{
{ "Ara" },
{ "Ken" },
{ "Eva" }
}
)

Now create individual measure for each owner

Ara_Pets =
CONCATENATEX (
FILTER (
Owner,
Owner[Owner] = "Ara" &&
Owner[Animal] = SELECTEDVALUE ( AnimalInfo[Animal])
),
Owner[Pet Name],
", ",
"n/a"
)

Ken_Pets =
CONCATENATEX (
FILTER (
Owner,
Owner[Owner] = "Ken" &&
Owner[Animal] = SELECTEDVALUE ( AnimalInfo[Animal])
),
Owner[Pet Name],
", ",
"n/a"
)

Eva_Pets =
CONCATENATEX (
FILTER (
Owner,
Owner[Owner] = "Eva" &&
Owner[Animal] = SELECTEDVALUE ( AnimalInfo[Animal])
),
Owner[Pet Name],
", ",
"n/a"
)

Hope it works

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[sudhisami_azure · Accepted solution]

Hi Syeena,

I found it much easier to achieve the desired result using Power Query and a matrix table. This method eliminates the need for DAX functions.

You can download the PBIX file from this link: [Download PBIX File](https://drive.google.com/file/d/1o6uTVY-O_wELcH4-6rK0Lr1Gr6Lo-eqT/view?usp=sharing).

Best regards,

[syeena]

thank you so much! this is very helpful

[rajendraongole1 · Accepted solution]

Hi @syeena - you can append these three sheets (Ara,Ken and Eva) into a single table named as Owner

Rename columns appropriately for consistency (e.g., Name of the Pet to Pet Name, Pet to Animal).

create a relationship between owner table and Animal tables as lik below

create a new table as 

OwnerMapping =
DATATABLE (
"Owner", STRING,
{
{ "Ara" },
{ "Ken" },
{ "Eva" }
}
)

Now create individual measure for each owner

Ara_Pets =
CONCATENATEX (
FILTER (
Owner,
Owner[Owner] = "Ara" &&
Owner[Animal] = SELECTEDVALUE ( AnimalInfo[Animal])
),
Owner[Pet Name],
", ",
"n/a"
)

Ken_Pets =
CONCATENATEX (
FILTER (
Owner,
Owner[Owner] = "Ken" &&
Owner[Animal] = SELECTEDVALUE ( AnimalInfo[Animal])
),
Owner[Pet Name],
", ",
"n/a"
)

Eva_Pets =
CONCATENATEX (
FILTER (
Owner,
Owner[Owner] = "Eva" &&
Owner[Animal] = SELECTEDVALUE ( AnimalInfo[Animal])
),
Owner[Pet Name],
", ",
"n/a"
)

Hope it works

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[syeena]

i tried this as well, it works! but quite difficult if these data are in a large volume