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.
Morning,
i have two tables, Table2 has multiple records to Table1
the tables are linked by Ref - so im looking to get the earliest date from table2[date_1] for each row in table1
then i need to get the earliest from table1[date] and table1[the new col]
Table 2:
Ref | date deliverd |
123456 | 01/01/2021 |
123789 | 07/01/2021 |
123456 | 04/01/2021 |
123789 | 03/01/2021 |
Table 1:
ref | date closed | first date deliverd | date actioned |
123456 | Returned from above table | =first date deliverd or date closed (depending on whats filled out) | |
123458 | 01/01/2021 | ||
123789 | Returned from above table | ||
123587 | 01/01/2021 |
hope this makes sense and thank you!
Solved! Go to Solution.
Hi, @sovereignauto
According to your description and sample data, I can clearly understand your requirement, I think you can achieve this using DAX to create two columns, you can follow my steps:
Create two calculated columns like this:
first date delivered =
MINX(FILTER(ALL('Table 2'),[ref]=EARLIER([ref])),[date deliverd])
date actioned =
SWITCH(
TRUE(),
[date closed]=BLANK(),[first date delivered],
[first date delivered]=BLANK(),[date closed],
IF([date closed]<=[first date delivered],[date closed],[first date delivered]))
And you can get what you want, like this:
You can download my test pbix file below
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @sovereignauto
According to your description and sample data, I can clearly understand your requirement, I think you can achieve this using DAX to create two columns, you can follow my steps:
Create two calculated columns like this:
first date delivered =
MINX(FILTER(ALL('Table 2'),[ref]=EARLIER([ref])),[date deliverd])
date actioned =
SWITCH(
TRUE(),
[date closed]=BLANK(),[first date delivered],
[first date delivered]=BLANK(),[date closed],
IF([date closed]<=[first date delivered],[date closed],[first date delivered]))
And you can get what you want, like this:
You can download my test pbix file below
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Not very sure I understood your question.
If you need a calculated column on Table2 to get the earliest date from a related Table1, you could try:
Column = MINX(RELATEDTABLE(Table2), 'Table2'[Date])
I have updated my post with a couple of tables
so i need the earliest date from the records with the same ref but then i also need to add a column to get "actioned date" from the new column and an exsisting column so think i need it in power query rather than DAX?
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 |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |