Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Asim
Helper I
Helper I

Count with specific conditions

‎02-06-2018 04:05 AM

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
)

 

 

 Count.png

 

Message 1 of 14
1,797 Views
0
3 ACCEPTED SOLUTIONS
Zubair_Muhammad
Community Champion
Community Champion
In response to Asim

‎02-07-2018 03:58 AM

@Asim

 

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 )

Regards
Zubair

Please try my custom visuals

View solution in original post

Message 7 of 14
1,870 Views
Zubair_Muhammad
Community Champion
Community Champion
In response to Zubair_Muhammad

‎02-07-2018 04:00 AM

@Asim

 

Countwithcondition.png


Regards
Zubair

Please try my custom visuals

View solution in original post

Message 8 of 14
1,869 Views
Zubair_Muhammad
Community Champion
Community Champion
In response to Asim

‎02-20-2018 11:15 PM

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 )

Regards
Zubair

Please try my custom visuals

View solution in original post

Message 13 of 14
1,824 Views
13 REPLIES 13
v-yuezhe-msft
Employee
Employee

‎02-06-2018 10:22 PM

@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)

1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 2 of 14
1,769 Views
0
Asim
Helper I
Helper I
In response to v-yuezhe-msft

‎02-06-2018 11:37 PM

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

 

 

 Count1.png

 

 

 

Count.png

 

 

Message 3 of 14
1,765 Views
0
v-yuezhe-msft
Employee
Employee
In response to Asim

‎02-06-2018 11:40 PM

@Asim,

Please help to share sample data of your original tables and post expected result here.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 4 of 14
1,760 Views
0
Asim
Helper I
Helper I
In response to v-yuezhe-msft

‎02-07-2018 12:05 AM

@v-yuezhe-msft

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:55E120N1setup353543589IIC37TCX6Y
1/16/2018 1:59E120M2halted13543687I2X0R3GESK
1/16/2018 2:00E120M2setup123543684I2X0R3GESKL
1/16/2018 2:12E120M2production673543684I2X0R3GESKL
1/16/2018 2:30E120N1production13543589IIC37TCX6Y
1/16/2018 2:31E120N1halted73543589IIC37TCX6Y
1/16/2018 2:38E120N1production1063543589IIC37TCX6Y
1/16/2018 3:19E120M2halted13543684I2X0R3GESKL
1/16/2018 3:20E120M2setup83543694I2X0R3GESKOS
1/16/2018 3:28E120M2production683543694I2X0R3GESKOS
1/16/2018 3:36E120M1setup603543677I3A0R4HESKKL
1/16/2018 4:24E120N1setup263543589IIC37TCX6R
1/16/2018 4:36E120M1production293543677I3A0R4HESKKL
1/16/2018 4:36E120M2halted13543694I2X0R3GESKOS
1/16/2018 4:37E120M2setup43543688I2X0R3GESK1L
1/16/2018 4:41E120M2production173543688I2X0R3GESK1L
1/16/2018 4:50E120N1halted43543589IIC37TCX6R
1/16/2018 4:54E120N1production1363543589IIC37TCX6R
1/16/2018 4:58E120M2setup33543690I2X2R3GPSK
1/16/2018 5:01E120M2production83543690I2X2R3GPSK
1/16/2018 5:05E120M1setup173543678I3A0R3HESKKL
1/16/2018 5:09E120M2setup43543689I2X0R3GKSKZ
1/16/2018 5:13E120M2production1273543689I2X0R3GKSKZ
1/16/2018 5:22E120M1production463543678I3A0R3HESKKL
1/16/2018 6:08E120M1setup123543822I3A0R3IESKKL
1/16/2018 6:20E120M1production253543822I3A0R3IESKKL
1/16/2018 6:45E120M1setup13543821I3A0R3IPSEK1
1/16/2018 6:46E120M1production353543821I3A0R3IPSEK1
1/16/2018 7:10E120N1setup83543589IIC37TCX6U
1/16/2018 7:18E120N1production423543589IIC37TCX6U
1/16/2018 7:20E120M2setup243543603I2X0R0HEHKZ
1/16/2018 7:21E120M1halted33543821I3A0R3IPSEK1
1/16/2018 7:24E120M1production363543821I3A0R3IPSEK1
1/16/2018 7:44E120M2halted43543603I2X0R0HEHKZ
1/16/2018 7:48E120M2production123543603I2X0R0HEHKZ
1/16/2018 8:00E120M1production233543821I3A0R3IPSEK1
1/16/2018 8:00E120M2production323543603I2X0R0HEHKZ
1/16/2018 8:00E120N1production513543589IIC37TCX6U
1/16/2018 8:23E120M1setup83543747I3D006GBPSEK
1/16/2018 8:31E120M1halted253543747I3D006GBPSEK
1/16/2018 8:32E120M2setup333543633I2X0R0GEHKZ02
1/16/2018 8:51E120N1halted13543589IIC37TCX6U
1/16/2018 8:52E120N1setup1003543809IIC61YCX6K
1/16/2018 8:56E120M1setup333543747I3D006GBPSEK
1/16/2018 9:05E120M2production2373543633I2X0R0GEHKZ02
1/16/2018 9:29E120M1halted33543747I3D006GBPSEK
1/16/2018 9:32E120M1production433543747I3D006GBPSEK
1/16/2018 10:15E120M1setup113543823I3A0R4IESKKL
1/16/2018 10:26E120M1production193543823I3A0R4IESKKL
1/16/2018 10:32E120N1halted53543809IIC61YCX6K
1/16/2018 10:37E120N1production1843543809IIC61YCX6K
1/16/2018 10:45E120M1setup133543824I3A0R2IESKK
1/16/2018 10:58E120M1production233543824I3A0R2IESKK
1/16/2018 11:21E120M1setup113543629I3D007FBESKK
1/16/2018 11:32E120M1production423543629I3D007FBESKK
1/16/2018 12:14E120M1setup363543693I3A0R3GESKKL
1/16/2018 12:50E120M1halted33543693I3A0R3GESKKL
1/16/2018 12:53E120M1production913543693I3A0R3GESKKL
1/16/2018 13:02E120M2setup153543630I2X0R5FPSKE
1/16/2018 13:17E120M2production433543630I2X0R5FPSKE
1/16/2018 13:41E120N1halted283543809IIC61YCX6K
1/16/2018 14:00E120M2halted13543630I2X0R5FPSKE
1/16/2018 14:01E120M2setup223543631I2X2R4FPSK
1/16/2018 14:09E120N1setup153543809IIC61YCX6K
1/16/2018 14:23E120M2production873543631I2X2R4FPSK
1/16/2018 14:24E120M1setup423543760I3A022LESKK
1/16/2018 14:24E120N1production2283543809IIC61YCX6K
1/16/2018 15:06E120M1production193543760I3A022LESKK
1/16/2018 15:25E120M1setup193543628I3D012FBESKK
1/16/2018 15:44E120M1production453543628I3D012FBESKK
1/16/2018 15:50E120M2halted13543631I2X2R4FPSK
1/16/2018 15:51E120M2setup273543740I2C005GBKSK
1/16/2018 16:18E120M2production2223543740I2C005GBKSK
1/16/2018 16:29E120M1setup523543614I3A044MESKK
1/16/2018 17:21E120M1production173543614I3A044MESKK
1/16/2018 17:38E120M1setup373543627I3D019FBESKK
1/16/2018 18:12E120N1halted53543809IIC61YCX6K
1/16/2018 18:15E120M1production643543627I3D019FBESKK
1/16/2018 18:17E120N1setup223543809IIC61YCX6K
1/16/2018 18:39E120N1production813543809IIC61YCX6K
1/16/2018 19:19E120M1setup413543609I5A033MPSEK1
1/16/2018 20:00E120M1setup463543609I5A033MPSEK1
1/16/2018 20:00E120M2production213543740I2C005GBKSK
1/16/2018 20:00E120N1production243543809IIC61YCX6K
1/16/2018 20:21E120M2setup33543741I2C005GBESK
1/16/2018 20:24E120M2production323543741I2C005GBESK
1/16/2018 20:24E120N1halted83543809IIC61YCX6K
1/16/2018 20:32E120N1halted1  
1/16/2018 20:33E120N1setup203543504IIC37T4X6K
1/16/2018 20:46E120M1setup743543835IIC19OCP4L
1/16/2018 20:53E120N1production513543504IIC37T4X6K
1/16/2018 20:56E120M2setup113543739I2C107GBPSK
1/16/2018 21:07E120M2production403543739I2C107GBPSK
1/16/2018 21:44E120N1halted403543504IIC37T4X6K
1/16/2018 21:47E120M2halted13543739I2C107GBPSK
1/16/2018 21:48E120M2setup83543742I2C003GBKSK
1/16/2018 21:56E120M2production643543742I2C003GBKSK
1/16/2018 22:00E120M1production6003543835IIC19OCP4L
1/16/2018 22:24E120N1production23543504IIC37T4X6K
1/16/2018 22:26E120N1halted13543504IIC37T4X6K
1/16/2018 22:27E120N1production1033543504IIC37T4X6K
1/16/2018 23:00E120M2setup133543743I2C003GBKSK
1/16/2018 23:13E120M2production2153543743I2C003GBKSK

 

 

 

regards

asim

Message 5 of 14
1,758 Views
0
Asim
Helper I
Helper I
In response to Asim

‎02-07-2018 12:09 AM

ultimatly count will be the desire result.

 

 

DateMachineEventDurationOrderSemi.Mat.No.Count
1/16/2018 1:55E120N1setup353543589IIC37TCX6Y1
1/16/2018 4:24E120N1setup263543589IIC37TCX6R1
1/16/2018 7:10E120N1setup83543589IIC37TCX6U1
1/16/2018 8:52E120N1setup1003543809IIC61YCX6K1
1/16/2018 14:09E120N1setup153543809IIC61YCX6K0
1/16/2018 18:17E120N1setup223543809IIC61YCX6K0
1/16/2018 20:33E120N1setup203543504IIC37T4X6K1
Message 6 of 14
1,757 Views
0
Zubair_Muhammad
Community Champion
Community Champion
In response to Asim

‎02-07-2018 03:58 AM

@Asim

 

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 )

Regards
Zubair

Please try my custom visuals
Message 7 of 14
1,871 Views
Zubair_Muhammad
Community Champion
Community Champion
In response to Zubair_Muhammad

‎02-07-2018 04:00 AM

@Asim

 

Countwithcondition.png


Regards
Zubair

Please try my custom visuals
Message 8 of 14
1,870 Views
Asim
Helper I
Helper I
In response to Zubair_Muhammad

‎02-19-2018 11:20 PM

@Zubair_Muhammad

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.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

setup count1.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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 )

Message 10 of 14
1,722 Views
0
Zubair_Muhammad
Community Champion
Community Champion
In response to Asim

‎02-20-2018 12:03 AM

@Asim

 

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 )
    )

Regards
Zubair

Please try my custom visuals
Message 11 of 14
1,384 Views
0
Asim
Helper I
Helper I
In response to Zubair_Muhammad

‎02-20-2018 12:27 AM

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

 

 

 

setup count2.png

 

 

Message 12 of 14
1,377 Views
0
Zubair_Muhammad
Community Champion
Community Champion
In response to Asim

‎02-20-2018 11:15 PM

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 )

Regards
Zubair

Please try my custom visuals
Message 13 of 14
1,825 Views
Asim
Helper I
Helper I
In response to Zubair_Muhammad

‎02-21-2018 09:22 PM

@Zubair_Muhammad,

This measure is working perfect without any negative impact on speed or RAM.

 

thank you very much for your help

 

regards

Asim

Message 14 of 14
1,363 Views
0
Asim
Helper I
Helper I
In response to Zubair_Muhammad

‎02-07-2018 07:55 PM

@Zubair_Muhammad

Good Morning!

Thank you very much dear, you made my life so easy, 

This solution is perfect!

 

 

Best Regards

Asim 

Message 9 of 14
1,737 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All