Forum Discussion

Hayleysea's avatar
Hayleysea
Icon for Resolver II rankResolver II
6 years ago
Solved

Calculated column based on whether value is available against ID

Hi There,   I have a table with project ID's, stages and dates. Stages range from 1 to 5 but not all project ID's have a stage 5 entry. In the case they don't have a Stage 5 entry I need to use Sta...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    6 years ago

    Hayleysea 

     

    Try this calculated column

     

    Column =
VAR Stage5_present =
    NOT (
        ISEMPTY (
            CALCULATETABLE (
                VALUES ( TableName[Stage] ),
                ALLEXCEPT ( TableName, TableName[Project ID] ),
                TableName[Stage] = 5
            )
        )
    )
VAR Value_I_Want =
    IF (
        Stage5_present,
        CALCULATE (
            MAX ( TableName[Date] ),
            ALLEXCEPT ( TableName, TableName[Project ID] ),
            TableName[Stage] = 5
        ),
        EDATE (
            CALCULATE (
                MAX ( TableName[Date] ),
                ALLEXCEPT ( TableName, TableName[Project ID] ),
                TableName[Stage] = 4
            ),
            1
        )
    )
RETURN
    IF ( [Stage] = 4, Value_I_Want )
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hayleysea please create a calculated column as per below

Column = 
VAR stage4date = CALCULATE(MAX(Test[Date]),ALLEXCEPT(Test,Test[Project ID]))
VAR _maxstage = CALCULATE(MAX(Test[Stage]),ALLEXCEPT(Test,Test[Project ID]))
RETURN SWITCH(TRUE()
                ,AND(Test[Stage]=4,_maxstage = 5),stage4date
                ,AND(Test[Stage]=4,_maxstage=4),DATEADD(Test[Date].[Date],1,MONTH)
                ,BLANK())

 

Hayleysea's avatar
Hayleysea
Icon for Resolver II rankResolver II
6 years ago

Thanks for the response. This is the error I get when implementing this solution. I have found the solution in another reply.

 

"Function 'SWITCH' does not support comparing values of type True/False with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values."