Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
maxduff59
Helper II
Helper II

Create a table to relate two other tables

Hello everybody, 

I am quite a newbie on Power BI so I am facing some difficulties. Indeed, I have many tables in my table but some of them are not related even if they have common aspects. Here is an example : 

Capture d’écran 2022-11-30 à 16.23.32.png

 By reading, some docs and lessons on learn.microsoft, they were showing an example with two tables (order and sales) and they create another table with date values in order to filter both tables (order and sales) by date. Such as like this : 

Capture d’écran 2022-11-30 à 16.25.17.png

I was wondering if it was possible  to do the same manipulation with a table of string values ( name of players ) in order to filter informations for each player.

Thanks in advance for your help

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

Here is one way of creating a dimension table using DAX. The source tables are:

Table 1:

t1.jpg

Table 2:

t2.jpg

(Note how the list of names is different between tables).

In the ribbon, under "Modeling", select new table and type in the equivalent to:

Name Table =
VAR _t1 =
    VALUES ( 'Table 1'[Player name] ) // Creates a table of unique values for table 1 names
VAR _t2 =
    VALUES ( 'Table 2'[Player name] ) // Creates a table of unique values for table 2 names
RETURN
    DISTINCT ( UNION ( _t1, _t2 ) )
// UNION appends the tables contained within, creating a table of all the values listed in tables _t1 & _t2. DISTINCT in turn removes the duplicate values

 

Now you need to create the relationship between this new Name Table and each corresponding field in the original tables. Select the name field in the Name Table and drag it to match the corresponding field in the other tables. Once they are created, double click on the relationship to check the nature of the relationship (you should "always" aim to create a one-to-many (or many-to-one in the GIF), single direction. Check that the fields are the correct ones. Visually you can see the direction of the relationship (single arrow) and that it is 1-to-* (one-to-many). Check that the fields are the correct ones.

Dim Table.gif

Sample PBIX file attached

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

2 REPLIES 2
PaulDBrown
Community Champion
Community Champion

Here is one way of creating a dimension table using DAX. The source tables are:

Table 1:

t1.jpg

Table 2:

t2.jpg

(Note how the list of names is different between tables).

In the ribbon, under "Modeling", select new table and type in the equivalent to:

Name Table =
VAR _t1 =
    VALUES ( 'Table 1'[Player name] ) // Creates a table of unique values for table 1 names
VAR _t2 =
    VALUES ( 'Table 2'[Player name] ) // Creates a table of unique values for table 2 names
RETURN
    DISTINCT ( UNION ( _t1, _t2 ) )
// UNION appends the tables contained within, creating a table of all the values listed in tables _t1 & _t2. DISTINCT in turn removes the duplicate values

 

Now you need to create the relationship between this new Name Table and each corresponding field in the original tables. Select the name field in the Name Table and drag it to match the corresponding field in the other tables. Once they are created, double click on the relationship to check the nature of the relationship (you should "always" aim to create a one-to-many (or many-to-one in the GIF), single direction. Check that the fields are the correct ones. Visually you can see the direction of the relationship (single arrow) and that it is 1-to-* (one-to-many). Check that the fields are the correct ones.

Dim Table.gif

Sample PBIX file attached

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






MAwwad
Super User
Super User

Yes it can be possible, you must connect the relationship from the Table (Name of players) and then set the filter direction from the names of player table to the other two tables, like that the Name of Players table will filter the two other tables

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.