Forum Discussion
Creating Relationship between 2 Different Datasets
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 |
Hi Anonymous - 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!!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,
4 Replies
Hi Anonymous - 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!!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,