Forum Discussion

meghansh's avatar
meghansh
Frequent Visitor
1 year ago
Solved

Need help with calculated column

Hello everyone,

 

I need help creating a calculated column with the following data and conditions:

IDNameCourse CodeEnd DateResult
12345RobertRed21-Mar-21Pass
12345RobertRed24-Apr-22Pending
12345RobertRed Pending
12345RobertBlue23-May-23Pending
12345RobertBlue30-Mar-24Pending
64537GeorgeRed31-May-23Pending
64537GeorgeRed31-Aug-23Pending

 


The requirement is to create a calculated column called "indicator" which fulfils this requirement:

1) If these both conditions are met: End Date is Blank and Result = Pending, then marks "Yes" for all rows with same Course Code for an ID. If these conditions are not met for any row, then mark "No".

The result shout be like:

IDNameCourse CodeEnd DateResultIndicator
12345RobertRed21-Mar-21PassYes
12345RobertRed24-Apr-22PendingYes
12345RobertRed PendingYes
12345RobertBlue23-May-23PendingNo
12345RobertBlue30-Mar-24PendingNo
64537GeorgeRed31-May-23PendingNo
64537GeorgeRed31-Aug-23PendingNo
Need Help
Show and Tell
  • lbendlin's avatar
    lbendlin
    1 year ago
    Indicator = 
var i = [ID] var cc=[Course Code]
var a = Filter('Table',[ID]=i && [Course Code]=cc && [Result]="Pending" && ISBLANK([End Date]))
return if(COUNTROWS(a)>0,"Yes","No")
  • Ashish_Mathur's avatar
    Ashish_Mathur
    1 year ago

    Hi,

    This calculated column formula works

    Column = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Course Code]=EARLIER(Data[Course Code])&&Data[End Date]=BLANK()&&Data[Result]="pending"))>0,"Yes","No")

    Hope this helps.

     

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago
    Indicator = 
var i = [ID] var cc=[Course Code]
var a = Filter('Table',[ID]=i && [Course Code]=cc && [Result]="Pending" && ISBLANK([End Date]))
return if(COUNTROWS(a)>0,"Yes","No")
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    This calculated column formula works

    Column = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Course Code]=EARLIER(Data[Course Code])&&Data[End Date]=BLANK()&&Data[Result]="pending"))>0,"Yes","No")

    Hope this helps.

     

  • Irwan's avatar
    Irwan
    Super User
    1 year ago

    hello meghansh 

     

    i think there are other ways to achive your need this but i would do something as below.

     

    1. create a new table to match the requirement (blank value in end date and result is pending).

    Summarize = 
    SUMMARIZE(
        FILTER(
            'Table',
            ISBLANK('Table'[End Date])&&
            'Table'[Result]="Pending"
        ),
        'Table'[ID],
        'Table'[Name],
        'Table'[Course Code]
    )
    2. create a new calculated column with following DAX.
    Indicator = 
    var _Value = 
    MAXX(
        FILTER(
            'Summarize',
            'Table'[Course Code]='Summarize'[Course Code]&&
            'Table'[ID]='Summarize'[ID]
        ),
        1
    )
    Return
    IF(
        _Value=1,
        "Yes",
        "No"
    )
     
    Hope this will help.
    Thank you.
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi meghansh ,

    Thanks for all the replies!
    And meghansh , please check whether their solutions will help you solve your problem?
    If solved please accept the reply in this post which you think is helpful as a solution to help more others facing the same problem to find a solution quickly, thank you very much!

    Best Regards,
    Dino Tao