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
Naresh91
Microsoft Employee
Microsoft Employee

How to count the lost category for each ID based on the statflag and Date

i have table like below and requirement is,
need to calculate the CategoryID for each Id whose ActiveFlag =1 and latest statflag is 0 and old statflag is 1

 

IDCategoryIDActiveflagstatflagcalenderDateCount
a1231104/14/20211
a1232114/14/20210
a1233114/14/20210
a1234114/14/20210
b2331101/1/20210
b2332101/1/20211
b2333111/1/20210
c2331111/1/20210
e3432112/6/20210
f2342102/6/20210
a1231112/6/20210
b2332112/6/20210
1 ACCEPTED SOLUTION

@Naresh91 , Try new column like

 

New column =
var _date = sumx(filter(Table, [ID] = earlier([ID]) && [CategoryID] =earlier([CategoryID]) && [calenderDate] <earlier([calenderDate])),[calenderDate])
var _statflag = sumx(filter(Table, [ID] = earlier([ID]) && [CategoryID] =earlier([CategoryID]) && [calenderDate] =_date),[statflag])
return
if([statflag] =0 && _statflag= 1, 1,0)

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@Naresh91 , Not very clear, can explain the output with examples

 

Hi @amitchandak ,

from the above data "a123" have statflag =1 for CategoryID 1 on "2/6/2021"
and "a123" have statflag =0 for CategoryID 1 on "4/14/2021"
 so it will consider as Lost ID and so count will be 1 

similar way for "b233" have statflag =1 for CategoryID 2 on "2/6/2021"
and "b233" have statflag =0 for CategoryID 2 on "1/1/2021"
so here also count is 1

 

so like this for the above data Lost count will be 2 as "a123" and "b233" lost their one CatogeryId  

 

 

@Naresh91 , Try new column like

 

New column =
var _date = sumx(filter(Table, [ID] = earlier([ID]) && [CategoryID] =earlier([CategoryID]) && [calenderDate] <earlier([calenderDate])),[calenderDate])
var _statflag = sumx(filter(Table, [ID] = earlier([ID]) && [CategoryID] =earlier([CategoryID]) && [calenderDate] =_date),[statflag])
return
if([statflag] =0 && _statflag= 1, 1,0)

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.