Need help with calculated column

[meghansh]

Hello everyone,

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

ID	Name	Course Code	End Date	Result
12345	Robert	Red	21-Mar-21	Pass
12345	Robert	Red	24-Apr-22	Pending
12345	Robert	Red		Pending
12345	Robert	Blue	23-May-23	Pending
12345	Robert	Blue	30-Mar-24	Pending
64537	George	Red	31-May-23	Pending
64537	George	Red	31-Aug-23	Pending

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:

ID	Name	Course Code	End Date	Result	Indicator
12345	Robert	Red	21-Mar-21	Pass	Yes
12345	Robert	Red	24-Apr-22	Pending	Yes
12345	Robert	Red		Pending	Yes
12345	Robert	Blue	23-May-23	Pending	No
12345	Robert	Blue	30-Mar-24	Pending	No
64537	George	Red	31-May-23	Pending	No
64537	George	Red	31-Aug-23	Pending	No

[lbendlin · Accepted solution]

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

[Anonymous]

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

[Ashish_Mathur · Accepted solution]

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.

[lbendlin · Accepted solution]

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

[Irwan]

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.