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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Yucy
Helper I
Helper I

Need help on if statement DAX

Hello all, 

I have a small table with category and state, I am looking for a new column. Can anyone help? Example is below:

 

example.gif

1 ACCEPTED SOLUTION

Try like

Column = var _res = COUNTX(filter(Sheet1,[State]="Resolved" && [Category] =EARLIER([Category])),[Category])+0
return if([State]="Resolved" && _res>0, [State],if([State]="Closed" && _res=0,[State],BLANK()))

 

File attached after signature 

 

View solution in original post

8 REPLIES 8
v-yuta-msft
Community Support
Community Support

@Yucy ,

 

I'm still not understand of the logic of how to create 'new column' you mentioned, could you please clarify more details?

 

Regards,

Jimmy Tao

Thanks for helping, my table has column A and B, Category and State, I need to insert a new column as column C. my if statement never works,

cmilligan262
Helper II
Helper II

@Yucy Can you provide more info on why some of the closed values in the state column correspond to a blank and why others correspond to closed

Sorry, my bad. The closed at new column for category C should be blank, but Closed at new column for category E should be closed because there is no resolved for E, then I want to keep the closed

Try like

Column = var _res = COUNTX(filter(Sheet1,[State]="Resolved" && [Category] =EARLIER([Category])),[Category])+0
return if([State]="Resolved" && _res>0, [State],if([State]="Closed" && _res=0,[State],BLANK()))

 

File attached after signature 

 

Thank you for your help, however, it not working for me,  maybe I did not provide enough data, Here is exactly what I have in my dataset, would you mind have a look again, I am sorry, thanks

case numbervalueOpenedENDColumn
CS0080746Resolved1/20/2020 10:291/20/2020 11:03Closed
CS0080746Resolved1/20/2020 10:291/23/2020 10:38Closed
CS0080746Closed1/20/2020 10:291/31/2020 0:00Closed
CS0080746Resolved1/20/2020 10:291/31/2020 0:00Closed
CS0080772Resolved1/20/2020 10:541/21/2020 14:11Closed
CS0080772Closed1/20/2020 10:542/01/2020 0:00Closed
CS0080772Resolved1/20/2020 10:542/01/2020 0:00Closed
CS0080798Closed1/20/2020 11:181/20/2020 13:22Closed
CS0089655Closed2/13/2020 13:342/13/2020 13:35Closed

@amitchandak

@Yucy 

Can you provide data in table format?

 

if you need more help mark me @

Appreciate your Kudos.

 

Thanks, here is the table

CategoryStateNew Column
AResolvedResolved
AResolvedResolved
AClosed 
BResolvedResolved
BClosed 
CResolvedResolved
CClosed 
CClosed 
DResolvedResolved
DResolvedResolved
DClosed 
DResolvedResolved
DResolvedResolved
E  
EClosedClosed

@amitchandak

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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