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 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. 🙂
Solved! Go to Solution.
Hi @niravd1992 ,
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
Hi @niravd1992 ,
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
Hey @niravd1992 , 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.
Thanks for the reply @ChrisClever , I tried this. But for some reason, output is coming as blanks
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 |
---|---|
40 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |