Forum Discussion

Yucy's avatar
Yucy
Icon for Helper I rankHelper I
6 years ago
Solved

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:  
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    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 

     

cmilligan262's avatar
cmilligan262
Icon for Helper II rankHelper II
6 years ago

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

  • Yucy's avatar
    Yucy
    Icon for Helper I rankHelper I
    6 years ago

    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

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      Yucy 

      Can you provide data in table format?

       

      if you need more help mark me @

      Appreciate your Kudos.

       

      • Yucy's avatar
        Yucy
        Icon for Helper I rankHelper I
        6 years ago

        Thanks, here is the table

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

        amitchandak

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      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 

       

      • Yucy's avatar
        Yucy
        Icon for Helper I rankHelper I
        6 years ago

        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