Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!See when key Fabric features will launch and what’s already live, all in one place and always up to date. Explore the new Fabric roadmap
Hi all,
I want to create a calculated column named 'project' to come true the picture shown as below,
The 'project' value is 'NEW_VALUE' based on 'ESN' and 'FIELD' value equals 'A'.
And between two 'FIELD' values euals 'A' based on 'CREATED_TS', the 'project' value euqals the older 'NEW_VALUE' value.
Sample data as below:
CREATED_TS | FIELD | NEW_VALUE | ESN |
2019/7/25 5:04 | A | Reg_KI10001.01_0725 | 1 |
2019/7/25 5:20 | A | Reg_KI10001.01_0724 | 1 |
2019/7/25 5:29 | A | Reg_KI10001.01_0723 | 1 |
2019/8/6 4:27 | A | Reg_KI10001.01_0805 | 1 |
2019/7/25 5:06 | A | Reg_KK10001.00_0725 | 2 |
2019/8/22 6:41 | A | Reg_KK10001.02_0822 | 2 |
2019/9/1 9:39 | B | else1 | 2 |
2019/9/1 9:45 | B | else2 | 2 |
2019/9/2 12:34 | B | else5 | 2 |
2019/9/2 12:34 | B | else4 | 2 |
2019/9/2 12:45 | B | else3 | 2 |
2019/9/2 13:15 | B | else6 | 2 |
2019/9/4 13:15 | A | Reg_SJ10001.02_0823 | 2 |
2019/9/4 14:25 | A | Reg_GI10001.02_0821 | 2 |
Please help me for this issue!
Thanks!
Solved! Go to Solution.
Hi,
Check if this works:
project = VAR v_lag = CALCULATE( MAX( Data[NEW_VALUE] ), FILTER( Data, Data[CREATED_TS] <= EARLIER( Data[CREATED_TS] ) ) ) RETURN IF( Data[FIELD] == "A", Data[NEW_VALUE], v_lag )
Here's what i got:
Thanks.
Hi,
Check if this works:
project = VAR v_lag = CALCULATE( MAX( Data[NEW_VALUE] ), FILTER( Data, Data[CREATED_TS] <= EARLIER( Data[CREATED_TS] ) ) ) RETURN IF( Data[FIELD] == "A", Data[NEW_VALUE], v_lag )
Here's what i got:
Thanks.
Hi,
I think "MAX( DATA[NEW_VALUE] )" does not works if "NEW_VALUE" like picture as below.
User | Count |
---|---|
88 | |
68 | |
67 | |
58 | |
53 |
User | Count |
---|---|
40 | |
38 | |
34 | |
32 | |
28 |