Calculate Product C/O

[Iamnvt]

hi,

How can I get the [Result] column as table blow? each time Product A change to B will be counted as 1, A to A in the same sequence will be counted as 0.

Day          Line         Product    Result

1	Line 1	A	0
1	Line 1	B	1
1	Line 1	B	0
2	Line 1	A	1
2	Line 1	A	0
2	Line 1	C	1
2	Line 1	A	1
1	Line 2	A	0
1	Line 2	B	1
1	Line 2	A	1
2	Line 2	A	0
2	Line 2	C	1

link to the file: https://1drv.ms/x/s!Aps8poidQa5zk8EUEsR_yqMdcELxOQ?e=KklMsz

Thanks

[V-pazhen-msft]

@Iamnvt 

The idea is comparing the current Product with previous Product. 

Column = 
var previousProduct = MAXX(FILTER('Table',[Index]=EARLIER([Index])-1),[Product])
Return IF([Index]=MIN([Index]),0,IF([Product]=previousProduct,0,1))

 

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens]

Hey @Iamnvt ,

basically it's possible, but only if you can provide some kind of index, that can be used to look into the "past" and to determine if a change has happened during the day.

There is no concept of previous row inside Power BI, for this it's necessary to create this. You can consider adding an index column using PowerQuery.

Hopefully, this provides some ideas. Maybe you can add this index to your sample data and upload the file again.

Regards,

Tom

[Iamnvt]

@TomMartens I got what you meant.

however, as I want the calculation to be dynamic, based on slicer selections. That needs a Virtual table and a DAX measure to resolve that.

is there any specific reasons why DAX not having the concept of Index? I find quite many scenarios using this, but would be pitiful if we can't

thanks

[TomMartens]

Hey @Iamnvt,

providing an Index column, does not necessarily mean it has to be a column with a numeric data type, also columns with the datatype datetime are possible. Something that allows ordering. Even filtering will not change the order. Data distribution might occur that two (or more events, depending on the number of lines) at the exact same time on the same day, but these events can always be separated by the line.

Basically, it's not that special that databases (the Analysis Services Tabular engine running inside Power BI) consider a table structure a heap (an unordered set of rows). That becomes ordered if an ordering index is applied.

Until there is a way to order the events inside a day, it will not be possible to create a measure. As I mentioned, it's not necessary to have an index per line and/or day, an index (date, integer) allowing to order the rows is sufficient.

Regards,

Tom
 

[Iamnvt]

@TomMartens I added the Index as below:

Index   Day   Line  Product   Result

1	1	Line 1	A	0
2	1	Line 1	B	1
3	1	Line 1	B	0
4	2	Line 1	A	1
5	2	Line 1	A	0
6	2	Line 1	C	1
7	2	Line 1	A	1
8	1	Line 2	A	0
9	1	Line 2	B	1
10	1	Line 2	A	1
11	2	Line 2	A	0
12	2	Line 2	C	1

What to do next to get the Result?

[TomMartens · Accepted solution]

Hey @Iamnvt ,

if there is an index, the idea to solve questions like this one, is always similar.

Find the previous event, that is respecting a given context.

The context describes a sequence (line), to solve these questions it's necessary to find the preceding "waypoint" on this sequence. 

One waypoint e.g. is described as
line: line 1 | Index: 3

As it can be possible that are many previous waypoints, it's necessary to find the waypoint that is immediately preceding the current one.

I created a calculated column using this DAX statement:

Column = 
//the waypoint
-- the sequence
--var __day = 'Table'[Day]
var __line = 'Table'[Line]
--the wapoint (current position)
var __index = 'Table'[Index]
//additional attribute
var __p  = 'Table'[Product]
var p = 
    CALCULATE(
        FIRSTNONBLANK('Table'[Product] , 0)
        , 
        TOPN(
            1
             , FILTER(ALL('Table') , 'Table'[Line] = __line 
             --&& 'Table'[Day] = __day 
             && 'Table'[Index] < __index)
             , 'Table'[Index]
             , DESC
        )   
    )
return
IF(OR(ISBLANK(p) , p = __p) , 0 , 1)

There is the variable p that stores the product of the previous waypoint in the current sequence (line). If the current waypoint marks the 1st waypoint in a sequence this variable returns blank.

To discover the row I use TOPN(). TOPN returns a table, I use this table to filter the base table; finally, I use FIRSTNONBLANK to extract the product from the filtered table.
A screenshot of my table:

Hopefully, this is what you are looking for.

Regards,
Tom