Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
i have 2 tables, both are coming from different data sources.
Table 1
ID Type Date
1 "Type":"A", "Group"="A" Aug- 16-2020
2 "Type":"A", "Group"="A" Aug -16-2020
3 "Type":"B", "Group"="B" Aug -15-2020
4 "Type":"B", "Group"="B" Aug-15-2020
Table 2
ID Type Date
1 "Type":"A", "Group"="A" Aug- 16-2020
2 "Type":"A", "Group"="A" Aug -16-2020
3 "Type":"B", "Group"="B" Aug -15-2020
4 "Type":"B", "Group"="B" Aug-15-2020
If i create relationship between "Table 1- ID" column and "Table 2 -ID" column, it would be one-to-one,
the relationship between "Table-1 Type" column and "Table 2-Type"coulmn is going to be many-to- many,
and between "Table-1 Date" column and "Table-2 Date" column is again many-to- many.
My reqirement is, i want to create a table visual with "Table 1" of "ID", "Type" columns and "Table 2", of "ID", "Type" Columns, and then i should create calculated columns/measures, if "Table 1" of "ID" = "Table 2" of "ID" then true, else false, and if "Table 1" of "Type" of "Group" (should compare substrings)= "Table 2" of "Type" of "Group" then true else false.
I am unable to add all the 4 columns to the visual (ID, Type from Table 1 & ID, Type from Table 2), Can anyone help me how to create a relationship between these two tables so that i can add all 4 columns to the table visual, and then create calculated column/measure.
Thanks in advance.
Solved! Go to Solution.
Hi @yodha ,
1. Add custom columns to the two tables respectively.
2. Spilt Column
3. Create a calculated column in table1.
Column =
IF(
Table1[ID] = RELATED(Table2[ID]) && Table1[Custom.2] = RELATED(Table2[Custom.2]),
TRUE(), FALSE()
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @yodha ,
1. Add custom columns to the two tables respectively.
2. Spilt Column
3. Create a calculated column in table1.
Column =
IF(
Table1[ID] = RELATED(Table2[ID]) && Table1[Custom.2] = RELATED(Table2[Custom.2]),
TRUE(), FALSE()
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @yodha
So you have Table1 and Table2, and [ID] column is unique between the tables.
--Go to PowerQuery window and select Table1
--in the toolbar click merge, see below pic
--Now select Table2 in the second table option, and select [ID] in both of the table for join.
--Ok, this will create a field and you can expand your columns from Table2, and from here you can build custom/calculated fields.
Hope this helps.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |