Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi there,
Good day!
I have been facing problem while trying to calculate the count of semi material number.
I have been using below mentioned formula in a calculated column to calculate the count of semi material number but it is not giving me the desire result,
The logic is that if machine is under setup and previous row and current row have the semi material number then i want to have 1 otherwise 0 in my calculated column.
Count = IF(
CALCULATE(
COUNTA(Eventlog[Machine_Material]),
FILTER(Eventlog,
Eventlog[Date] > EARLIER(Eventlog[Date])
&& (Eventlog[Machine_Material]) = EARLIER(Eventlog[Machine_Material])
)
)> 1,
0,
1
)
Is there someone help me out please.
Regards
Asim
Solved! Go to Solution.
Hi @Asim,
I'd like to suggest you use below measure formula to instead.
Sample:
Count = VAR filtered = COUNTROWS ( FILTER ( Eventlog, Eventlog[Date] > MAX ( Eventlog[Date] ) && ( Eventlog[Machine_Material] ) = SELECTEDVALUE ( Eventlog[Machine_Material] ) ) ) RETURN IF ( filtered > 1, 0, 1 )
BTW, you can also use below formula if you had to use calculate column to achieve your requirement:
Count = IF ( CALCULATE ( COUNTA ( Eventlog[Machine_Material] ), FILTER ( ALL ( Eventlog ), Eventlog[Date] > EARLIER ( Eventlog[Date] ) && ( Eventlog[Machine_Material] ) = EARLIER ( Eventlog[Machine_Material] ) ) ) > 1, 0, 1 )
Regards,
Xiaoxin Sheng
Hi @Asim,
I'd like to suggest you use below measure formula to instead.
Sample:
Count = VAR filtered = COUNTROWS ( FILTER ( Eventlog, Eventlog[Date] > MAX ( Eventlog[Date] ) && ( Eventlog[Machine_Material] ) = SELECTEDVALUE ( Eventlog[Machine_Material] ) ) ) RETURN IF ( filtered > 1, 0, 1 )
BTW, you can also use below formula if you had to use calculate column to achieve your requirement:
Count = IF ( CALCULATE ( COUNTA ( Eventlog[Machine_Material] ), FILTER ( ALL ( Eventlog ), Eventlog[Date] > EARLIER ( Eventlog[Date] ) && ( Eventlog[Machine_Material] ) = EARLIER ( Eventlog[Machine_Material] ) ) ) > 1, 0, 1 )
Regards,
Xiaoxin Sheng
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |