Measure: If column has

[HenryJS]

Hi all,

How do I create a column which:

If 'Candidate' has 'Candidate Declaration' & 'CIS Verification' THEN Yes

[v-alq-msft]

Hi, @HenryJS 

Based on your description, I created data to reproduce your scenario.

Table:

You may create a measure as below.

Yes/No = 
var _candidate = SELECTEDVALUE('Table'[Candidate])
return
IF(
    ISFILTERED('Table'[Candidate]),
    IF(
        AND(
            COUNTROWS(
                FILTER(
                    ALLSELECTED('Table'),
                    'Table'[Candidate] = _candidate&&
                    'Table'[Document] = "Candidate Declaration"
                )
            )>0,
            COUNTROWS(
                FILTER(
                    ALLSELECTED('Table'),
                    'Table'[Candidate] = _candidate&&
                    'Table'[Document] = "CIS Verification"
                )
            )>0
        ),
        "Yes",
        "No"
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HenryJS]

@amitchandak  can you help?

[amitchandak]

@HenryJS 

Try

column = if(calculate(distinctcount([document]),filter(table, table[candidate]= earlier(Table[table[candidate]]) 
									&& [document] in {"Candidate Declaration" , "CIS Verification"}))=2, "Yes","No")
									
or

column = if(calculate(distinctcount([document]),filter(table, table[candidate]= earlier(Table[table[candidate]]) 
									&& earlier([document]) in {"Candidate Declaration" , "CIS Verification"}))=2, "Yes","No")								

[HenryJS]

Hi @amitchandak 

I tried this

column = if(calculate(distinctcount([document]),filter('query-results', 'query-results'[Candidate Name]= earlier('query-results'[Candidate Name])

                                    && earlier([document]) in {"Candidate Declaration" , "CIS Verification"}))=2, "Yes","No")

But it didn't work it just gave "No" for everything when there should definitely be "Yes"

[amitchandak · Accepted solution]

@HenryJS , Can you provide sample data