topic [Help Needed] - How to compare and get values from two tables without a relationship in DAX Commands and Tips

[Help Needed] - How to compare and get values from two tables without a relationship

Anonymous — Tue, 07 Jun 2022 07:22:13 GMT

Hi Everyone,

Requesting your help in making a table with Dax

Attached a image above explaining the need.

Table 1 has three columns . Table 2 has two columns.

Output should be from Table 1 - Serial Numbers which are Shipped,but the values should be selected only when Date in table 2 is more than 5 days compared to Date in Table 1.

There is no relationship between tables.
amitchandak

Thanks in Advance. 🙂

Re: [Help Needed] - How to compare and get values from two tables without a relationship

ChrisClever — Tue, 07 Jun 2022 10:05:29 GMT

Hey Anonymous , take a look at this DAX code.

TimeDifference = CALCULATE ( DATEDIFF ( SELECTEDVALUE ( 'Table 1'[Date] ), SELECTEDVALUE ( 'Table 2'[Date] ), DAY ), TREATAS ( VALUES ( 'Table 1'[Serial Number] ), 'Table 2'[Serial Number] ) )

This is how you can calculate the time difference.

From there just use filters.

Re: [Help Needed] - How to compare and get values from two tables without a relationship

Anonymous — Fri, 10 Jun 2022 06:04:09 GMT

Hi Anonymous ,

Here are the steps you can follow：

1. Create calculated column.

Date2 = CALCULATE(MAX('Table2'[Date]),FILTER(ALL(Table2),'Table2'[Serial Number]=EARLIER('Table1'[Serial Number])))

Day = DATEDIFF('Table1'[Date],'Table1'[Date2],DAY)

2. Create calculated table.

Table = var _1=FILTER(ALL(Table1),'Table1'[Day]>=5&&'Table1'[Status]="Shipped") return SUMMARIZE( _1, 'Table1'[Serial Number],'Table1'[Date],Table1[Status],Table1[Day])

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: [Help Needed] - How to compare and get values from two tables without a relationship

Anonymous — Fri, 10 Jun 2022 06:58:15 GMT

Thanks for the reply ChrisClever , I tried this. But for some reason, output is coming as blanks