Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi guys!
I have 2 tables and i need to create e 3rd one.
the first table is Table 1:
Serial number | name | phone number |
aaa1 | ||
aaa2 | ||
aaan |
Table 2:
serial number | payment date |
aaa1 | 1/4/20 |
aaa2 | 5/5/20 |
aaa1 | 3/5/20 |
aaa1 | 2/6/20 |
aaa2 | 4/6/20 |
aaa2 | 25/8/20 |
aaan | ... |
I need to create a table which contains a column with all the serial numbers as unique values for each raw and another column with the difference in number of days between today and the FIRST payment date they made for instance for aaa1 the difference would be 11/01/2021-1/4/2020=(guess)200 days
Table 3:
Serial number | Difference of date [datediff(today, first payment date)] |
aaa1 | 200 days |
aaa2 | 120 days |
aaa3 | 50 days |
aaan | n days |
I'm not sure if I should create a calculated column or a measure because when I create a column close to the serial number column it looks like it doesn't give me the difference of days between the first payment related to that specific serial number.
Does anyone know how to help me?
thanks!
date Difference between dates Calculation @Tapemeasure Getting aggregation type of a measure issue
Solved! Go to Solution.
Hi @carol_mar ,
Based on your description, you can use LOOKUPVALUE() to create calculated columns in table 2 which meets conditions from table 1 and table 3:
Gender years =
VAR a =
LOOKUPVALUE (
'Table 1'[Account Number],
'Table 1'[Customer name], 'Table 2'[Serial Number]
)
RETURN
LOOKUPVALUE ( 'Table 3'[GenderYears], 'Table 3'[Account Number], a )
long =
VAR a =
LOOKUPVALUE (
'Table 1'[Account Number],
'Table 1'[Customer name], 'Table 2'[Serial Number]
)
RETURN
LOOKUPVALUE ( 'Table 3'[long], 'Table 3'[Account Number], a )
Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @carol_mar ,
Based on your description, you can create this calculated table as Table 3:
Table 3 =
SUMMARIZE (
'Table 1',
'Table 1'[Serial number],
"Difference of date",
DATEDIFF (
CALCULATE (
MIN ( 'Table 2'[payment date] ),
FILTER (
ALL ( 'Table 2' ),
'Table 2'[Serial number] = EARLIER ( 'Table 1'[Serial number] )
)
),
TODAY (),
DAY
)
)
Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much, I'll try that right now!
I have another question for this table:
I have 3 tables, T1 and T2 have the same column containing "serial number", T2 and T3 have the same column containing "Customer Account Number". I need to have in T3 the column "Serial Number" and each serial number is a unique value corresponding to the "serial number". The number of raws in the tables are all different, T1 is the biggest one while T3 the smallest table. How can I do that? In excel would just be an "index and compare" or a "xlookup". Thank you so so much!
Hi @carol_mar ,
Not certain what is your expected output.. Could you please consider provide example tables and expected output which is like your initial post for further discussion?
Best Regards,
Community Support Team _ Yingjie Li
Hi,
First, thank you for your reply.
Below a more detailed explanation of what I need, I have 3 tables:
Table 1
Customer name | Customer Surname | Serial Number | Account Number |
aaa | zzz | 123 | a1a1 |
bbb | vvv | 456 | b2b2 |
ccc | uuu | 789 | c3c3 |
Table 2:
Serial Number | Customer name | phone number | status |
aaa | |||
bbb | |||
ccc |
Table 3:
Account Number | Gender | Years | long |
a1a1 | |||
b2b2 | |||
c3c3 |
I need to add a column in Table 2 that contains for each specific serial number the gender, latitude, and longitude that are in Table 3 but identified by the Account Number.
Is this more clear? Thank you 🙂
Hi @carol_mar ,
Based on your description, you can use LOOKUPVALUE() to create calculated columns in table 2 which meets conditions from table 1 and table 3:
Gender years =
VAR a =
LOOKUPVALUE (
'Table 1'[Account Number],
'Table 1'[Customer name], 'Table 2'[Serial Number]
)
RETURN
LOOKUPVALUE ( 'Table 3'[GenderYears], 'Table 3'[Account Number], a )
long =
VAR a =
LOOKUPVALUE (
'Table 1'[Account Number],
'Table 1'[Customer name], 'Table 2'[Serial Number]
)
RETURN
LOOKUPVALUE ( 'Table 3'[long], 'Table 3'[Account Number], a )
Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You can have measure for that
datediff(min(tAb2[paymentdate]), max(tab2[paymentdate]), Day) wnd analyze that with coommon dimension of serial no or first table if joined.
or a column in tabbed colum = datediff(
minx(filter(tab2, tab2[serial no] = tab1[serial no]), tab2[payment date]),
maxx(filter(tab2, tab2[serial no] = tab1[serial no]), tab2[payment date]), day)
Hi! Thank you so much for your reply.
I did try that (calculated column) and it worked on the table itself (apparently) but the issue I had with it was that when I try to visualize the values on a report table it doesn't show any value in that column that's why I thought it was wrong. Do you know why?
@carol_mar , Strange. from which table you have used the serial number in this case. There few blank values for mismatch of the serial number.
Can you share a sample pbix after removing sensitive data, it this not working even after having the correct column/s and aggregation
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
60 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |