Count number of change over of a field over a period of time

[vissvess]

Hi all,

 I need a measure that counts the changes in a particular period.

For example, in a production line, say the mapping of product occurs and timestamp is being captured for the process.

In an hour window, say first 10 units belong to Family A followed by 15 units belong to family B, 5 units of family A, 3 units of family C and so on.

I need a measure that provides me thee number of changeover occured on top of filter applied on date,hour and conveyor selection.

I need the community folks help to get me an idea on how to approach the requirement and the schema or syntax of the dax required.!!!!

Kindly Help me in this regard

Vissvess 

[kentyler]

so your "fact" table might look like this

Production Date	Production Hour	Product Family	Quantity Produced	Conveyor
1/1/2019	1	a	33	23
1/1/2019	3	b	46	12
1/1/2019	4	c	57	44
1/1/2019	5	d	45	22
1/1/2019	7	e	34	44

It would probably be useful to have an "Hours" table with entries 1 thru 24 that could be in a relationship with your Hour column...might help to handle cases where there was no production in some hours.you probably want to add a "calendar table" as a dimension for your dates (you can find lots of information about how to create a calendar table online)

You probably would also want a "product family" table with a column you could link to the product family column, that might contain other information about product families you would want to group count or slice by. You might also find a "conveyer" dimension table useful. This will give you a star schema, with the fact table having a many to one relationship with all of your dimension tables.

if you place data from a table like this on a matrix it will probably be easy to have rows for date/hour/conveyor and then have it count the number of families. You probably would not have to write dax to produce a report like that.

[vissvess]

Hi @kentyler , 

Thanks for the effort.

Consider the data as follows.

Production Date	Production Hour	Product Family	Time	Conveyor
1/1/2019	1	a	01:10:10	23
1/1/2019	1	b	01:10:25	23
1/1/2019	1	a	01:10:42	23
1/1/2019	1	a	01:11:01	23
1/1/2019	1	b	01:12:10	23

and so on..

So, the date is same and the conveyor is same. 

The product output time and itz family is captured. Hour will be hour value of the timestamp.

Now I consider changeover as 1(a-->b), 2(b-->a) 3(a-->b) again and so on.. till the hour (or day) ends.

Changeover is from any state to anystate despite historical presence which is different from number of family in any production hour. Hope I explained for your understanding.

Sorry for not sharing any datamodel or pbix file as they are onto organizational privacy.

Kindly do the needful.

Thanks

[kentyler]

So does each new record represent one of the changes you are trying to count ? If so then your count might just be a COUNTROWS() over the period, minus 1 to not count the start row.

[vissvess]

NO... @kentyler ,

Count rows would not help. If the earlier entry is same as the current one.. No counting to happen.

'X' should be one up if there is difference between earlier and current..

Anyone kindly help me to sort this out.

@HotChilli , @Stachu , @Anonymous , @Greg_Deckler , @Zubair_Muhammad ....

Thanks in advance.

[vissvess]

Hi,

For the above my query on number of changeover in the specified period, an Idea sprung up.

I request the community folks to get me out of this with this idea....

I need a calculated column that holds the value of earlier family and concatenate with current entry family.

IF both (current and earlier families) are same, It should return blank else the concatenated family name should be there.

Then I shall count the number of entries for me to get the changeover.

Kindly help @Nathaniel_C  @Mariusz  @Anonymous  @sturlaws  @Anonymous  @Zubair_Muhammad  @HotChilli  @Stachu @parry2k @LAndes 

Thanks in advance.

[Zubair_Muhammad]

@vissvess 

Try this MEASURE

Put Conveyor and this MEASURE in a table visual

Measure =
SUMX (
    ADDCOLUMNS (
        'TableName',
        "myCount",
        VAR PreviousFamily =
            MINX (
                TOPN (
                    1,
                    FILTER ( 'TableName', [Time] < EARLIER ( 'TableName'[Time] ) ),
                    [Time], DESC
                ),
                [Product Family]
            )
        RETURN
            IF (
                OR ( ISBLANK ( PreviousFamily ), PreviousFamily = [Product Family] ),
                0,
                1
            )
    ),
    [myCount]
)

[vissvess]

@kentyler @Anonymous @Greg_Deckler @Zubair_Muhammad