Forum Discussion

RKM's avatar
RKM
Icon for Helper IV rankHelper IV
4 years ago
Solved

Derived Column based on Previous Value in Same

Dear Experts,

I have apparantly simple problem but finding it hard to resolve.

So, I got a table with 2 columns in Power BI as below.

Item ID----Session

A ----1

A ----2

B ----1

B ----4

B ----5

 

I need to find, how many Item arrived in a Session which was not in Previous Session. i.e.

Session ----New Item ----Comment
1 ----2 ----Both A & B
2 ----0 ----Nothing New, Since A was there on Session 1 already
3 ----0 ----Nothing
4 ----1 ----B - In this occasion B appeared in Session 4 but it was not there in Session 3
5 ----0 ----Nothing new, Since B was there on session 4 already

 

Now to achieve that basically what I am looking for is a "Derived Column" as below...

 

Item_IDIn_SessionDerived_IsNew
A1Y
A2N
B1Y
B4Y
B5N

 

Is this possible that way please? 

Another alternate hack someone suggested me is create another derived table etc. but if the above (Derived Column) idea is feasible then I dont want to go that way.

  • RKM , Try a new column like

     

    new column =
    var _cnt = countx(filter(Table, [In_Session] = earlier([In_Session]) -1 && [Item_ID] = earlier([Item_ID])), [Item_ID])
    return
    if( isblank(_cnt) , "Yes", "No")

2 Replies

  • RKM , Try a new column like

     

    new column =
    var _cnt = countx(filter(Table, [In_Session] = earlier([In_Session]) -1 && [Item_ID] = earlier([Item_ID])), [Item_ID])
    return
    if( isblank(_cnt) , "Yes", "No")