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: ID Name Course Code End Date Result 12345 Robert Red 21-Mar-21 Pass 12345 Rober...
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.

     

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.