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 All,
I'm trying to lookup some information from Table 1 below to Table 2.
Table 1 - Sales info:
Customer | Product | Sales | Contract Start Date | Contract End Date | Fiscal Year of Order Date |
A | Z | 100 | 1/1/2018 | 12/31/2018 | 2018 |
A | Y | 200 | 1/1/2018 | 12/31/2018 | 2018 |
A | Z | 200 | 4/1/2018 | 12/31/2018 | 2018 |
B | Y | 400 | 9/1/2018 | 4/1/2019 | 2018 |
B | Y | 300 | 1/1/2019 | 9/30/2020 | 2019 |
A | Z | 150 | 10/1/2019 | 4/1/2020 | 2019 |
Table2 - Issues Info:
Customer | Product | Total No. of Issues | Issue Date |
A | Z | 2 | 1/1/2018 |
A | Y | 5 | 1/1/2018 |
A | Z | 3 | 5/4/2018 |
B | Y | 7 | 12/30/2018 |
B | Y | 3 | 8/1/2020 |
A | Z | 2 | 11/23/2019 |
Ultimately, we would like to have Table 2 looks like below with additional column lookup-ed from Table 1 (Fiscal Year of Order Date):
Customer | Product | Total No. of Issues | Issue Date | Fiscal Year of Issue Date |
A | Z | 2 | 1/1/2018 | 2018 |
A | Y | 5 | 1/1/2018 | 2018 |
A | Z | 3 | 5/4/2018 | 2018 |
B | Y | 7 | 12/30/2018 | 2018 |
B | Y | 3 | 8/1/2020 | 2019 |
A | Z | 2 | 11/23/2019 | 2019 |
'Fiscal Year of Order Date' is match in Table 2 if and only if the Issue Date falls between the 'Contract Start Date' and 'Contract End Date'.
I've tried to create additional column "Key" by concatenate "Customer" & "Product". Then I've linked them up between both tables as Many-to-many relationship.
Below are the expressions that I have tried out by using the function RELATED(),
Solved! Go to Solution.
Thank you! @Anonymous
This works well with slight modifications per below:
Column =
VAR Prod = 'Issues Info'[Product]
VAR Cust = 'Sales Info'[Customer]
VAR _issue = 'Issues Info'[Issue Date ]
VAR _result = CALCULATE(MAX('Sales Info'[Fiscal Year of Order Date ]),FILTER('Sales Info','Sales Info'[Contract Start Date ]<=_issue && 'Sales Info'[Contract End Date ]>=_issue && 'Sales Info'[Product] = Prod &&'Sales Info'[Customer]= Cust))
RETURN _result
@JOO13 Create this calculated column in Issue Info table
Column =
VAR _issue = 'Issues Info'[Issue Date ]
VAR _result = CALCULATE(MAX('Sales Info'[Fiscal Year of Order Date ]),FILTER('Sales Info','Sales Info'[Contract Start Date ]<=_issue && 'Sales Info'[Contract End Date ]>=_issue && 'Sales Info'[Product] = 'Issues Info'[Product ]&&'Sales Info'[Customer]='Issues Info'[Customer ]))
RETURN _result
and check if it works for you
Thank you! @Anonymous
This works well with slight modifications per below:
Column =
VAR Prod = 'Issues Info'[Product]
VAR Cust = 'Sales Info'[Customer]
VAR _issue = 'Issues Info'[Issue Date ]
VAR _result = CALCULATE(MAX('Sales Info'[Fiscal Year of Order Date ]),FILTER('Sales Info','Sales Info'[Contract Start Date ]<=_issue && 'Sales Info'[Contract End Date ]>=_issue && 'Sales Info'[Product] = Prod &&'Sales Info'[Customer]= Cust))
RETURN _result
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 |
---|---|
107 | |
98 | |
78 | |
65 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |