Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi team,
I need to implement below requirement in DAX.
I have two tables like below. Both the tables are not related to each other and they are not related to any common tables(Sample tables below)
Table 1
pdt | cst | Acv |
P1 | C1 | 0.1 |
P2 | C2 | 1.1 |
P3 | C3 | 2.1 |
P4 | C4 | 3.1 |
P5 | C5 | 4.1 |
Table 2
pdt | cst | tgt |
P1 | C1 | 2.2 |
P2 | C2 | 3.2 |
P3 | C3 | 4.2 |
P4 | C4 | 5.2 |
P5 | C5 | 6.2 |
The tables have multiple common columns.I want to create a calculated table which will have the columns from two tables which are joined based on the multiple common columns between them something like below(Similar to inner join in SQL)
pdt | cst | Acv | tgt |
P1 | C1 | 0.1 | 2.2 |
P2 | C2 | 1.1 | 3.2 |
P3 | C3 | 2.1 | 4.2 |
P4 | C4 | 3.1 | 5.2 |
P5 | C5 | 4.1 | 6.2 |
The join should be based on both pdt and cst columns.
I tried using Summarize with Union and Treatas options but no luck.
Can this be done.Please suggest.
Thanks,
Sam
Thanks for taking time to reply @V-pazhen-msft / @amitchandak . I used treatas with NaturalInnerJoin and it worked.
Thanks,
Sam
@Anonymous
Is there any reason you cannot not have an active relationship between the tables. You could create this relationship between the [pdt] columns and leave it as Inactive relationship (dotted line). Then create the following measure:
Measure = CALCULATE(SUM('Table (2)'[tgt]),USERELATIONSHIP('Table'[pdt],'Table (2)'[pdt]),FILTER('Table (2)',[pdt]=MAX('Table'[pdt])))
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , you can create one/two common dimensions of pdt and cst. and join with both the tables and analyze them.
If need you can create a combine key.
Key = [pdt] & "-" & [cst]
refer
https://www.knowledgehut.com/blog/business-intelligence-and-visualization/power-bi-best-practices
@amitchandak ..I have many columns in actual tables and I cannot create new tables.Also I dont have any of these columns in the visual, but I require these columns in one table to perform further analysis.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |