Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello all,
I'm trying to make a DAX measure that returns the first date a discrepancy happens for each material in a pivot table but the formula that i've created didn't give me the right result: CALCULATE(MIN('BD 1'[Date]);FILTER(ALLEXCEPT('BD 1';'BD 1'[Material]);[ECRT 4400 UNR]<>0)).
[ECRT 4400 UNR]: a measure that allows me to only filter the materials with a stock gap.
This an example of the result that i had.
Material | Date | ECRT 4400 UNR | Discrepancy Date |
XPSAF5130 | 26/01/2022 | 1 | 17/01/2022 |
10/03/2022 | 1 | 17/01/2022 | |
11/03/2022 | 1 | 17/01/2022 | |
13/03/2022 | 1 | 17/01/2022 | |
14/03/2022 | 1 | 17/01/2022 | |
15/03/2022 | 1 | 17/01/2022 | |
16/03/2022 | 1 | 17/01/2022 | |
17/03/2022 | 1 | 17/01/2022 | |
18/03/2022 | 1 | 17/01/2022 | |
19/03/2022 | 1 | 17/01/2022 | |
20/03/2022 | 1 | 17/01/2022 | |
21/03/2022 | 1 | 17/01/2022 | |
22/03/2022 | 1 | 17/01/2022 | |
23/03/2022 | 1 | 17/01/2022 | |
24/03/2022 | 1 | 17/01/2022 | |
25/03/2022 | 1 | 17/01/2022 | |
27/03/2022 | 1 | 17/01/2022 | |
28/03/2022 | 1 | 17/01/2022 |
NB: I'm workingwith EXCEL.
Thank you for your help!
Solved! Go to Solution.
Hi @09
you may try
Measure1 :=
MINX (
FILTER (
CALCULATETABLE ( 'BD 1', ALLEXCEPT ( 'BD 1', 'BD 1'[Material] ) ),
[ECRT 4400 UNR] <> 0
),
'BD 1'[Date]
)
Hi @09
you may try
Measure1 :=
MINX (
FILTER (
CALCULATETABLE ( 'BD 1', ALLEXCEPT ( 'BD 1', 'BD 1'[Material] ) ),
[ECRT 4400 UNR] <> 0
),
'BD 1'[Date]
)
Thank you. It works perfectly.
@09 , Try like
minx(filter(values('BD 1'[Material]),[ECRT 4400 UNR]<>0), calculate( MIN('BD 1'[Date])))
Thanks for replying. I tried your formula but it gave me that result.
User | Count |
---|---|
58 | |
21 | |
18 | |
16 | |
13 |
User | Count |
---|---|
85 | |
54 | |
45 | |
39 | |
21 |