Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello Folks,
I have 2 given tables. Now I want to create a new column in table as New_Appliance where if there is a date difference between two dates in table 1 then look for value from table 2 where we can get the appliance name from Re-inspection date which is the lowest. For example: For Id 2, the new_appliance value should be Device.
Table1
Id | Inspection Date | Re-inspection Date | Appliance |
1 | 01/01/2020 | 01/01/2020 | Mobile |
2 | 15/02/2020 | 17/02/2020 | Tablet |
3 | 20/02/2020 | 23/02/2020 | Tablet |
4 | 25/02/2020 | 25/02/2020 | Device |
5 | 27/02/2020 | 01/03/2020 | Device |
Table 2
Id | Inspection Date | Re-inspection Date | Appliance |
1 | 01/01/2020 | 01/01/2020 | Mobile |
2 | 15/02/2020 | 15/07/2020 | Device |
2 | 15/02/2020 | 16/02/2020 | Mobile |
2 | 15/02/2020 | 17/02/2020 | Device |
3 | 20/02/2020 | 20/02/2020 | Mobile |
3 | 20/02/2020 | 21/02/2020 | Tablet |
3 | 20/02/2020 | 23/02/2020 | Device |
4 | 25/02/2020 | 25/02/2020 | Device |
5 | 27/02/2020 | 27/02/2020 | Device |
5 | 27/02/2020 | 28/02/2020 | Mobile |
5 | 27/02/2020 | 01/03/2020 | Device |
Expected Table
What I need is if there is a date difference in inspection & re-inspection date in table 1, I want to know what device was used initially to record the transaction:
This is how the new calculated column look like:
Id | Inspection Date | Re-inspection Date | Appliance | New_Appliance |
1 | 01/01/2020 | 01/01/2020 | Mobile | Mobile |
2 | 15/02/2020 | 17/02/2020 | Tablet | Device |
3 | 20/02/2020 | 23/02/2020 | Tablet | Mobile |
4 | 25/02/2020 | 25/02/2020 | Device | Device |
5 | 27/02/2020 | 01/03/2020 | Device | Device |
Solved! Go to Solution.
@Anonymous
Please add the following column to your Table 1. Your results are not in line with the explanation that you provided. ID 2, but the lowest should be "Mobile" as the lowest date is 16/2/2020.
New Appliance =
var __id = 'Table-1'[Id] return
IF(
'Table-1'[Inspection Date] = 'Table-1'[Re-inspection Date] ,
'Table-1'[Appliance],
var __mindate =
MINX(
FILTER(
'Table-2',
'Table-2'[Id] = __id
),
'Table-2'[Re-inspection Date]
)
var __device =
MAXX(
FILTER(
'Table-2',
'Table-2'[Id] = __id && 'Table-2'[Re-inspection Date] = __mindate
),
'Table-2'[Appliance]
)
return
__device
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
Please add the following column to your Table 1. Your results are not in line with the explanation that you provided. ID 2, but the lowest should be "Mobile" as the lowest date is 16/2/2020.
New Appliance =
var __id = 'Table-1'[Id] return
IF(
'Table-1'[Inspection Date] = 'Table-1'[Re-inspection Date] ,
'Table-1'[Appliance],
var __mindate =
MINX(
FILTER(
'Table-2',
'Table-2'[Id] = __id
),
'Table-2'[Re-inspection Date]
)
var __device =
MAXX(
FILTER(
'Table-2',
'Table-2'[Id] = __id && 'Table-2'[Re-inspection Date] = __mindate
),
'Table-2'[Appliance]
)
return
__device
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
For ID3, why should the answer be Mobile?
Because initally when the inspection was performed, mobile was used
Hi,
So if there is difference in the 2 dates of any ID in Table1, then you want to search for the Inspection date appearing in Table1 in the Inspection Date of Table2 and then bring over the Device from Table2 to Table1. is my understanding correct?
Yes, That's Right
For ID3, there are two different Appliances on 20/02/2020. Why should the answer be Mobile (and not Tablet)?
Hi,
Please show the expected result.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
146 | |
72 | |
63 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |