Help for VLook up type of rquirement

[ramhariessentia]

Hi Power BI Gurus,

Refering below image I am looking for a DAX formula for creating a column that gives the result "Converted") if there are two specific values "1.2 Design Checking" & 13.1 Inspection associated with Project ID, for example, project id 103176 has Design Certification & Inspection Please help me create that?

[v-luwang-msft · Accepted solution]

Hi @ramhariessentia ,

You could try the following measure:

test = 
VAR test3 =
    IF (
        CALCULATE (
            COUNT ( 'Sample'[Project ID] ),
            FILTER (
                ALL ( 'Sample' ),
                'Sample'[Fee Name] = "13.1 Inspection"
                    && 'Sample'[Project ID] = MAX ( 'Sample'[Project ID] )
            )
        ) >= 1,
        1,
        0
    )
VAR test2 =
    IF (
        CALCULATE (
            COUNT ( 'Sample'[Project ID] ),
            FILTER (
                ALL ( 'Sample' ),
                'Sample'[Fee Name] = "1.2 Design Checking"
                    && 'Sample'[Project ID] = MAX ( 'Sample'[Project ID] )
            )
        ) >= 1,
        1,
        0
    )
RETURN
    IF (
        test2 = 1
            && test3 = 1,
        "1.2 Design Checking&&13.1 Inspection",
        MAX ( 'Sample'[Fee Name] )
    )

Final you will get the below:

[amitchandak]

@ramhariessentia , Based on what I got, Try a new column like

if( calculate(distinctCOUNT([Fee Name]), filter(Table, [projectID] =earlier([projectID]) && [Fee Name] in {"1.2 Design Checking" , "13.1 Inspection (L1)"}))+0 =2, 1,0)

[ramhariessentia]

I am getting following syntax error

[ramhariessentia]