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
First post and a bit of a newbie!!!!! So bear with me..... I have 2 tables of data which is basically the same. One set of data is just 2 weeks older.
What I am trying to do a date difference between the 2.
Table1
Computer Name
Date Scanned
Table2
Computer Name
Date Scanned
I am trying to work out the date difference between the date scanned. There a relationship on between the tables based on computer name... There some other measures, calculated fields etc in the Table1
I have tried various variation of DateDiff including add related / MAX / MIN etc etc and still stuck....
Basically I want to do on Table2 a
DayDifferent = DATEDIFF(Table1[Date Scanned], Table2[Date Scanned],DAYS) <-- this is an example btw....
It wont list the Table1 field and just calculated fields if I do something this....
Related throws up a relationship problem
and MAX just give me 1 numbers as it is get MAX value for the date.... when I want it to do it for all of them
Do I need to put in Computer name to match ie IF computer from table1and table 2 is equal do a date diff.....
Completely stuck 😐 :S
Any help will be greatly apprieciated
Solved! Go to Solution.
Hi @AndyNeo ,
If the relationship is 1:1 or 1:*, you can create the diff measure like this:
_Diff =
DATEDIFF (
CALCULATE (
MAX ( 'Table 1'[Date Scanned] ),
FILTER (
ALL ( 'Table 1' ),
'Table 1'[Computer Name] in DISTINCT('Table 2'[Computer Name])
)
),
MAX('Table 2'[Date Scanned]),
DAY
)
If the relationship is *:*, you may want to calcualte the datediff for each computer of each row in table2, in this case, need to create index column in these two tables as auxiliary column in power query(see the steps in my below sample), then create a measure like this:
__Diff =
DATEDIFF (
CALCULATE (
MAX ( 'Table_1'[Date Scanned] ),
FILTER (
ALL ( Table_1 ),
'Table_1'[Computer Name]
IN DISTINCT ( 'Table_2'[Computer Name] )
&& 'Table_1'[Index] IN DISTINCT ( 'Table_2'[Index] )
)
),
CALCULATE (
MAX ( 'Table_2'[Scanned] ),
FILTER (
ALL ( Table_2 ),
'Table_2'[Computer Name]
IN DISTINCT ( 'Table_1'[Computer Name] )
&& 'Table_2'[Index] IN DISTINCT ( 'Table_2'[Index] )
)
),
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.
@AndyNeo
Have you tried the LOOKUPVALUE function: https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax
Video: https://www.youtube.com/watch?v=iJfzfYt5Qws&t=0s
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
FYI that if your two tables only differ on dates, you should append the two tables to have a single table in your model.
For your current model, if the relationship is 1:1 or 1:Many from Table2 to Table1 (and they are related on computer name), then this expression should work as a calculated column on Table2
Diff = DATEDIFF(MAX(Table1[Date Scanned]), Table2[Date Scanned], DAY)
Again, you really should append those tables, in which you will then need a different expression. And I would suggest you do this analysis as a measure, and not as a column. And add a Date table to your model. Ok, enough preaching.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi Pat
Are you saying to append the tables and then try that forumla....?
If I try that forumla as a measure I am only getting the existing measures list for use in the formula?
I think the problem maybe one table has 20000 rows and the other has 20100 rows.....
Hi @AndyNeo ,
If the relationship is 1:1 or 1:*, you can create the diff measure like this:
_Diff =
DATEDIFF (
CALCULATE (
MAX ( 'Table 1'[Date Scanned] ),
FILTER (
ALL ( 'Table 1' ),
'Table 1'[Computer Name] in DISTINCT('Table 2'[Computer Name])
)
),
MAX('Table 2'[Date Scanned]),
DAY
)
If the relationship is *:*, you may want to calcualte the datediff for each computer of each row in table2, in this case, need to create index column in these two tables as auxiliary column in power query(see the steps in my below sample), then create a measure like this:
__Diff =
DATEDIFF (
CALCULATE (
MAX ( 'Table_1'[Date Scanned] ),
FILTER (
ALL ( Table_1 ),
'Table_1'[Computer Name]
IN DISTINCT ( 'Table_2'[Computer Name] )
&& 'Table_1'[Index] IN DISTINCT ( 'Table_2'[Index] )
)
),
CALCULATE (
MAX ( 'Table_2'[Scanned] ),
FILTER (
ALL ( Table_2 ),
'Table_2'[Computer Name]
IN DISTINCT ( 'Table_1'[Computer Name] )
&& 'Table_2'[Index] IN DISTINCT ( 'Table_2'[Index] )
)
),
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.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |