Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
RKM
Helper IV
Helper IV

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.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

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

Thanks a lot @amitchandak . Exactly what I wanted.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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