Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 :
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 :
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
Solved! Go to Solution.
Here is one way of creating a dimension table using DAX. The source tables are:
Table 1:
Table 2:
(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.
Sample PBIX file attached
Proud to be a Super User!
Paul on Linkedin.
Here is one way of creating a dimension table using DAX. The source tables are:
Table 1:
Table 2:
(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.
Sample PBIX file attached
Proud to be a Super User!
Paul on Linkedin.
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |