Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |