Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all
I need your help/advice on how to calculate "Result-first export date" from table below.
Basically, For each ID on each row, it should first check through column "Export/Import" and find "EXP" and then return the minimum export date from "Date" column in the result column. Say for example: For ID= A12, first should find where is the EXP located under "Export/Import", it's on row#3&8, so the minimum Export date is on row#8, now it should fill in 3/2/2018 for all the rows that are related to ID=A12.
# | ID | Export/Import | Date | Result- First export date |
1 | C11 | IMP | 1/1/2018 | 5/2/2018 |
2 | B12 | IMP | 2/1/2019 | 2/1/2019 |
3 | A12 | EXP | 4/10/2022 | 3/2/2018 |
4 | A12 | IMP | 1/1/2018 | 3/2/2018 |
5 | B12 | EXP | 2/1/2019 | 2/1/2019 |
6 | A12 | IMP | 2/1/2018 | 3/2/2018 |
7 | C11 | EXP | 5/2/2018 | 5/2/2018 |
8 | A12 | EXP | 3/2/2018 | 3/2/2018 |
9 | A12 | IMP | 3/11/2018 | 3/2/2018 |
I kindly appreciate your time and effort here.
Thanks in advance,
Sanaz
Solved! Go to Solution.
Hi @Sanaz ,
Check the formula.
Column = CALCULATE(MIN('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[ID]),'Table'[Export/Import]="EXP"))
Hi @Sanaz ,
Check the formula.
Column = CALCULATE(MIN('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[ID]),'Table'[Export/Import]="EXP"))
Hi Sanaz,
You can make a calculated table and relate it to this table by "ID", see screenshot below.
DAX for calculated table ("Table" is original table, "MinDate" is calculated table):
MinDate =
GROUPBY(
FILTER(
'Table',
'Table'[Export/Import] = "EXP"
),
'Table'[ID],
'Table'[Export/Import],
"FirstExpDate" ,
MINX(
CURRENTGROUP(),
[Date]
)
)
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |