Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello!
Good day,
I would like to know the count of my semi material number is material is getting change, if not then i want to have 0 in column or measure.
I am using below mentioned dax formula in my calculated column to evalute the results but it is not working for some unknown reason.
Is there any one help me out from this situation.
Best Regards
Asim
Count = IF(
CALCULATE(
COUNTA(Eventlog[Machine_Material]),
FILTER(
Eventlog,
Eventlog[Date] > EARLIER(Eventlog[Date])
&& Eventlog[Semi.Mat.No.] <> EARLIER(Eventlog[Semi.Mat.No.])
)
) > 1,
0,
1
)
Solved! Go to Solution.
Try this calculated column
Column = VAR PreviousDate = CALCULATE ( MAX ( EventLog[Date] ), FILTER ( ALLEXCEPT ( EventLog, EventLog[Machine] ), EventLog[Event] = "setUp" && EventLog[Date] < EARLIER ( EventLog[Date] ) ) ) VAR PreviousMaterial = CALCULATE ( FIRSTNONBLANK ( EventLog[Semi.Mat.No.], 1 ), FILTER ( ALLEXCEPT ( EventLog, EventLog[Machine] ), EventLog[Event] = "setUp" && EventLog[Date] = PreviousDate ) ) RETURN IF ( EventLog[Semi.Mat.No.] = PreviousMaterial, 0, 1 )
Hi @Asim
Try this MEASURE... I am not sure if it will speed up things
Setup Count Measure = VAR mytable = ADDCOLUMNS ( Eventlog, "Asim", VAR PreviousDate = CALCULATE ( MAX ( EventLog[Date] ), FILTER ( ALLEXCEPT ( EventLog, EventLog[Machine] ), EventLog[Event] = "setup" && EventLog[Date] < EARLIER ( EventLog[Date] ) ) ) VAR PreviousMaterial = CALCULATE ( FIRSTNONBLANK ( EventLog[Semi.Mat.No.], 1 ), FILTER ( ALLEXCEPT ( EventLog, EventLog[Machine] ), EventLog[Event] = "setup" && EventLog[Date] = PreviousDate ) ) RETURN IF ( EventLog[Semi.Mat.No.] = PreviousMaterial, BLANK (), 1 ) ) RETURN COUNTX ( FILTER ( mytable, [Asim] = 1 ), 1 )
@Asim,
Create the following columns in your table.
index = CALCULATE(COUNT(Eventlog[Date]),ALL(Eventlog),FILTER(Eventlog,Eventlog[Date]<=EARLIER(Eventlog[Date])))
Column = var previous=CALCULATE( FIRSTNONBLANK(Eventlog[Semi.Mat.No],1),FILTER(Eventlog,Eventlog[index]=EARLIER(Eventlog[index])-1)) return IF(Eventlog[Semi.Mat.No]=previous,0,1)
Regards,
Lydia
Dear Lydia,
Thanks for your reply,
Actually the table is getting filtered by date by machine code and by event, in this case it was showing the records for machine E120N1 with event= setup and with date 15/1/2018.
after applying your suggested columns, I am getting below mentioned result.
I also attached one table without applying filter.
Regards
Asim
@Asim,
Please help to share sample data of your original tables and post expected result here.
Regards,
Lydia
I am new in this site so dont know how to attach the file, however i have copy the table below.
all i want is to have machine setup counts for each semi material, but if the setup for same material is repeating then the count should be one only.
1/16/2018 1:55 | E120N1 | setup | 35 | 3543589 | IIC37TCX6Y |
1/16/2018 1:59 | E120M2 | halted | 1 | 3543687 | I2X0R3GESK |
1/16/2018 2:00 | E120M2 | setup | 12 | 3543684 | I2X0R3GESKL |
1/16/2018 2:12 | E120M2 | production | 67 | 3543684 | I2X0R3GESKL |
1/16/2018 2:30 | E120N1 | production | 1 | 3543589 | IIC37TCX6Y |
1/16/2018 2:31 | E120N1 | halted | 7 | 3543589 | IIC37TCX6Y |
1/16/2018 2:38 | E120N1 | production | 106 | 3543589 | IIC37TCX6Y |
1/16/2018 3:19 | E120M2 | halted | 1 | 3543684 | I2X0R3GESKL |
1/16/2018 3:20 | E120M2 | setup | 8 | 3543694 | I2X0R3GESKOS |
1/16/2018 3:28 | E120M2 | production | 68 | 3543694 | I2X0R3GESKOS |
1/16/2018 3:36 | E120M1 | setup | 60 | 3543677 | I3A0R4HESKKL |
1/16/2018 4:24 | E120N1 | setup | 26 | 3543589 | IIC37TCX6R |
1/16/2018 4:36 | E120M1 | production | 29 | 3543677 | I3A0R4HESKKL |
1/16/2018 4:36 | E120M2 | halted | 1 | 3543694 | I2X0R3GESKOS |
1/16/2018 4:37 | E120M2 | setup | 4 | 3543688 | I2X0R3GESK1L |
1/16/2018 4:41 | E120M2 | production | 17 | 3543688 | I2X0R3GESK1L |
1/16/2018 4:50 | E120N1 | halted | 4 | 3543589 | IIC37TCX6R |
1/16/2018 4:54 | E120N1 | production | 136 | 3543589 | IIC37TCX6R |
1/16/2018 4:58 | E120M2 | setup | 3 | 3543690 | I2X2R3GPSK |
1/16/2018 5:01 | E120M2 | production | 8 | 3543690 | I2X2R3GPSK |
1/16/2018 5:05 | E120M1 | setup | 17 | 3543678 | I3A0R3HESKKL |
1/16/2018 5:09 | E120M2 | setup | 4 | 3543689 | I2X0R3GKSKZ |
1/16/2018 5:13 | E120M2 | production | 127 | 3543689 | I2X0R3GKSKZ |
1/16/2018 5:22 | E120M1 | production | 46 | 3543678 | I3A0R3HESKKL |
1/16/2018 6:08 | E120M1 | setup | 12 | 3543822 | I3A0R3IESKKL |
1/16/2018 6:20 | E120M1 | production | 25 | 3543822 | I3A0R3IESKKL |
1/16/2018 6:45 | E120M1 | setup | 1 | 3543821 | I3A0R3IPSEK1 |
1/16/2018 6:46 | E120M1 | production | 35 | 3543821 | I3A0R3IPSEK1 |
1/16/2018 7:10 | E120N1 | setup | 8 | 3543589 | IIC37TCX6U |
1/16/2018 7:18 | E120N1 | production | 42 | 3543589 | IIC37TCX6U |
1/16/2018 7:20 | E120M2 | setup | 24 | 3543603 | I2X0R0HEHKZ |
1/16/2018 7:21 | E120M1 | halted | 3 | 3543821 | I3A0R3IPSEK1 |
1/16/2018 7:24 | E120M1 | production | 36 | 3543821 | I3A0R3IPSEK1 |
1/16/2018 7:44 | E120M2 | halted | 4 | 3543603 | I2X0R0HEHKZ |
1/16/2018 7:48 | E120M2 | production | 12 | 3543603 | I2X0R0HEHKZ |
1/16/2018 8:00 | E120M1 | production | 23 | 3543821 | I3A0R3IPSEK1 |
1/16/2018 8:00 | E120M2 | production | 32 | 3543603 | I2X0R0HEHKZ |
1/16/2018 8:00 | E120N1 | production | 51 | 3543589 | IIC37TCX6U |
1/16/2018 8:23 | E120M1 | setup | 8 | 3543747 | I3D006GBPSEK |
1/16/2018 8:31 | E120M1 | halted | 25 | 3543747 | I3D006GBPSEK |
1/16/2018 8:32 | E120M2 | setup | 33 | 3543633 | I2X0R0GEHKZ02 |
1/16/2018 8:51 | E120N1 | halted | 1 | 3543589 | IIC37TCX6U |
1/16/2018 8:52 | E120N1 | setup | 100 | 3543809 | IIC61YCX6K |
1/16/2018 8:56 | E120M1 | setup | 33 | 3543747 | I3D006GBPSEK |
1/16/2018 9:05 | E120M2 | production | 237 | 3543633 | I2X0R0GEHKZ02 |
1/16/2018 9:29 | E120M1 | halted | 3 | 3543747 | I3D006GBPSEK |
1/16/2018 9:32 | E120M1 | production | 43 | 3543747 | I3D006GBPSEK |
1/16/2018 10:15 | E120M1 | setup | 11 | 3543823 | I3A0R4IESKKL |
1/16/2018 10:26 | E120M1 | production | 19 | 3543823 | I3A0R4IESKKL |
1/16/2018 10:32 | E120N1 | halted | 5 | 3543809 | IIC61YCX6K |
1/16/2018 10:37 | E120N1 | production | 184 | 3543809 | IIC61YCX6K |
1/16/2018 10:45 | E120M1 | setup | 13 | 3543824 | I3A0R2IESKK |
1/16/2018 10:58 | E120M1 | production | 23 | 3543824 | I3A0R2IESKK |
1/16/2018 11:21 | E120M1 | setup | 11 | 3543629 | I3D007FBESKK |
1/16/2018 11:32 | E120M1 | production | 42 | 3543629 | I3D007FBESKK |
1/16/2018 12:14 | E120M1 | setup | 36 | 3543693 | I3A0R3GESKKL |
1/16/2018 12:50 | E120M1 | halted | 3 | 3543693 | I3A0R3GESKKL |
1/16/2018 12:53 | E120M1 | production | 91 | 3543693 | I3A0R3GESKKL |
1/16/2018 13:02 | E120M2 | setup | 15 | 3543630 | I2X0R5FPSKE |
1/16/2018 13:17 | E120M2 | production | 43 | 3543630 | I2X0R5FPSKE |
1/16/2018 13:41 | E120N1 | halted | 28 | 3543809 | IIC61YCX6K |
1/16/2018 14:00 | E120M2 | halted | 1 | 3543630 | I2X0R5FPSKE |
1/16/2018 14:01 | E120M2 | setup | 22 | 3543631 | I2X2R4FPSK |
1/16/2018 14:09 | E120N1 | setup | 15 | 3543809 | IIC61YCX6K |
1/16/2018 14:23 | E120M2 | production | 87 | 3543631 | I2X2R4FPSK |
1/16/2018 14:24 | E120M1 | setup | 42 | 3543760 | I3A022LESKK |
1/16/2018 14:24 | E120N1 | production | 228 | 3543809 | IIC61YCX6K |
1/16/2018 15:06 | E120M1 | production | 19 | 3543760 | I3A022LESKK |
1/16/2018 15:25 | E120M1 | setup | 19 | 3543628 | I3D012FBESKK |
1/16/2018 15:44 | E120M1 | production | 45 | 3543628 | I3D012FBESKK |
1/16/2018 15:50 | E120M2 | halted | 1 | 3543631 | I2X2R4FPSK |
1/16/2018 15:51 | E120M2 | setup | 27 | 3543740 | I2C005GBKSK |
1/16/2018 16:18 | E120M2 | production | 222 | 3543740 | I2C005GBKSK |
1/16/2018 16:29 | E120M1 | setup | 52 | 3543614 | I3A044MESKK |
1/16/2018 17:21 | E120M1 | production | 17 | 3543614 | I3A044MESKK |
1/16/2018 17:38 | E120M1 | setup | 37 | 3543627 | I3D019FBESKK |
1/16/2018 18:12 | E120N1 | halted | 5 | 3543809 | IIC61YCX6K |
1/16/2018 18:15 | E120M1 | production | 64 | 3543627 | I3D019FBESKK |
1/16/2018 18:17 | E120N1 | setup | 22 | 3543809 | IIC61YCX6K |
1/16/2018 18:39 | E120N1 | production | 81 | 3543809 | IIC61YCX6K |
1/16/2018 19:19 | E120M1 | setup | 41 | 3543609 | I5A033MPSEK1 |
1/16/2018 20:00 | E120M1 | setup | 46 | 3543609 | I5A033MPSEK1 |
1/16/2018 20:00 | E120M2 | production | 21 | 3543740 | I2C005GBKSK |
1/16/2018 20:00 | E120N1 | production | 24 | 3543809 | IIC61YCX6K |
1/16/2018 20:21 | E120M2 | setup | 3 | 3543741 | I2C005GBESK |
1/16/2018 20:24 | E120M2 | production | 32 | 3543741 | I2C005GBESK |
1/16/2018 20:24 | E120N1 | halted | 8 | 3543809 | IIC61YCX6K |
1/16/2018 20:32 | E120N1 | halted | 1 | ||
1/16/2018 20:33 | E120N1 | setup | 20 | 3543504 | IIC37T4X6K |
1/16/2018 20:46 | E120M1 | setup | 74 | 3543835 | IIC19OCP4L |
1/16/2018 20:53 | E120N1 | production | 51 | 3543504 | IIC37T4X6K |
1/16/2018 20:56 | E120M2 | setup | 11 | 3543739 | I2C107GBPSK |
1/16/2018 21:07 | E120M2 | production | 40 | 3543739 | I2C107GBPSK |
1/16/2018 21:44 | E120N1 | halted | 40 | 3543504 | IIC37T4X6K |
1/16/2018 21:47 | E120M2 | halted | 1 | 3543739 | I2C107GBPSK |
1/16/2018 21:48 | E120M2 | setup | 8 | 3543742 | I2C003GBKSK |
1/16/2018 21:56 | E120M2 | production | 64 | 3543742 | I2C003GBKSK |
1/16/2018 22:00 | E120M1 | production | 600 | 3543835 | IIC19OCP4L |
1/16/2018 22:24 | E120N1 | production | 2 | 3543504 | IIC37T4X6K |
1/16/2018 22:26 | E120N1 | halted | 1 | 3543504 | IIC37T4X6K |
1/16/2018 22:27 | E120N1 | production | 103 | 3543504 | IIC37T4X6K |
1/16/2018 23:00 | E120M2 | setup | 13 | 3543743 | I2C003GBKSK |
1/16/2018 23:13 | E120M2 | production | 215 | 3543743 | I2C003GBKSK |
regards
asim
ultimatly count will be the desire result.
Date | Machine | Event | Duration | Order | Semi.Mat.No. | Count |
1/16/2018 1:55 | E120N1 | setup | 35 | 3543589 | IIC37TCX6Y | 1 |
1/16/2018 4:24 | E120N1 | setup | 26 | 3543589 | IIC37TCX6R | 1 |
1/16/2018 7:10 | E120N1 | setup | 8 | 3543589 | IIC37TCX6U | 1 |
1/16/2018 8:52 | E120N1 | setup | 100 | 3543809 | IIC61YCX6K | 1 |
1/16/2018 14:09 | E120N1 | setup | 15 | 3543809 | IIC61YCX6K | 0 |
1/16/2018 18:17 | E120N1 | setup | 22 | 3543809 | IIC61YCX6K | 0 |
1/16/2018 20:33 | E120N1 | setup | 20 | 3543504 | IIC37T4X6K | 1 |
Try this calculated column
Column = VAR PreviousDate = CALCULATE ( MAX ( EventLog[Date] ), FILTER ( ALLEXCEPT ( EventLog, EventLog[Machine] ), EventLog[Event] = "setUp" && EventLog[Date] < EARLIER ( EventLog[Date] ) ) ) VAR PreviousMaterial = CALCULATE ( FIRSTNONBLANK ( EventLog[Semi.Mat.No.], 1 ), FILTER ( ALLEXCEPT ( EventLog, EventLog[Machine] ), EventLog[Event] = "setUp" && EventLog[Date] = PreviousDate ) ) RETURN IF ( EventLog[Semi.Mat.No.] = PreviousMaterial, 0, 1 )
Dear, the calculated column which you suggested earlier is working perfect except consuming RAM, The solution using below mentioned measure you suggested is not returning the desire result.
could you please check!
Regards
Asim
Setup Count =
VAR PreviousDate =
CALCULATE (
MAX ( EventLog[Date] ),
FILTER (
ALLEXCEPT ( EventLog, EventLog[Machine] ),
EventLog[Event] = "setup"
&& EventLog[Date] < SELECTEDVALUE ( EventLog[Date] )
)
)
VAR PreviousMaterial =
CALCULATE (
FIRSTNONBLANK ( EventLog[Semi.Mat.No.], 1 ),
FILTER (
ALLEXCEPT ( EventLog, EventLog[Machine] ),
EventLog[Event] = "setup"
&& EventLog[Date] = PreviousDate
)
)
RETURN
IF ( SELECTEDVALUE ( EventLog[Semi.Mat.No.] ) = PreviousMaterial, BLANK (), 1 )
Give this a shot
Setup Count = VAR PreviousDate = CALCULATE ( MAX ( EventLog[Date] ), FILTER ( ALLEXCEPT ( EventLog, EventLog[Machine] ), EventLog[Event] = "setup" && EventLog[Date] < SELECTEDVALUE ( EventLog[Date] ) ) ) VAR PreviousMaterial = CALCULATE ( FIRSTNONBLANK ( EventLog[Semi.Mat.No.], 1 ), FILTER ( ALLEXCEPT ( EventLog, EventLog[Machine] ), EventLog[Event] = "setup" && EventLog[Date] = PreviousDate ) ) RETURN SUMX ( SUMMARIZE ( EventLog, EventLog[Machine], EventLog[Semi.Mat.No.] ), IF ( EventLog[Semi.Mat.No.] = PreviousMaterial, BLANK (), 1 ) )
Dear @Zubair_Muhammad,
After putting new formula in measure i am getting below result which is not as per desire result.
In fact Calculated column is giving precise result but the problem is that its occupy huge memory which create problem when the data grow.
Please help!
Regards
Asim
Hi @Asim
Try this MEASURE... I am not sure if it will speed up things
Setup Count Measure = VAR mytable = ADDCOLUMNS ( Eventlog, "Asim", VAR PreviousDate = CALCULATE ( MAX ( EventLog[Date] ), FILTER ( ALLEXCEPT ( EventLog, EventLog[Machine] ), EventLog[Event] = "setup" && EventLog[Date] < EARLIER ( EventLog[Date] ) ) ) VAR PreviousMaterial = CALCULATE ( FIRSTNONBLANK ( EventLog[Semi.Mat.No.], 1 ), FILTER ( ALLEXCEPT ( EventLog, EventLog[Machine] ), EventLog[Event] = "setup" && EventLog[Date] = PreviousDate ) ) RETURN IF ( EventLog[Semi.Mat.No.] = PreviousMaterial, BLANK (), 1 ) ) RETURN COUNTX ( FILTER ( mytable, [Asim] = 1 ), 1 )
This measure is working perfect without any negative impact on speed or RAM.
thank you very much for your help
regards
Asim
Good Morning!
Thank you very much dear, you made my life so easy,
This solution is perfect!
Best Regards
Asim
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |