Cannot convert value of type text to true/false

[Turka89]

Hi Community,

I have two tables Requisition and Application. Requisition has a division column and its values are  IT, HR, Finance. Application has column called current status of which have a values of interview and hired. 

What I would like to see is how many applicant on interview per Division values(IT,HR,Finance) and represented by numbers if its true and 0 if its false. 

Here is  a look of the tables:

I've tried this DAX if it makes any sense i get the aforementioned error on title 

Measure = CALCULATE(COUNTX(APPLICATION, APPLICATION[CurrentStatus] = "ITI - Assessment"), IF(SELECTEDVALUE(REQUISITION[Division]), APPLICATION[CurrentStatus])) + 0

Result in error

What I would like to see

Thank you so mucn in advance.

[v-juanli-msft · Accepted solution]

Hi @Turka89 

I'm not clear what's your rule to get the final result.

Please check my test below and tell me what's the difference between yours and mine.

Measure = CALCULATE(COUNTROWS(FILTER(Table2,Table2[CurrentStatus]="IT-Assessment")),ALLEXCEPT(Table1,Table1[Division]))

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @Turka89 

Is the [VacancyID] in Requisition table unique?

Best Regards

Maggie

[Turka89]

Hi @v-juanli-msft , 

In Requisition Table, I cant really find any duplicate entrines under VacancyID Column but only for those VacancyID under CurrentStatus column "ITI 1st Interview, ITI - 2nd Interview, ITI - Assessment, ITI - Final Assessment". Otherwise There is duplicate entries in VacancyID column in Requisition Table

The Above picture VacnacyID values which have current status of  one of the following values

1. ITI 1st Interview

2. ITI - 2nd Interview

3. ITI - Assessment

4. ITI - Final Assessment

Regardless, I have used the same column "CurrentStatus" with other Values such as 

1. Hired

2. On-Boarding

Which returns correct results to a specific divisions.

What I cannot understand why it does not wants to wrok with any of the "Interview" CurrentStatus Column 

Thank you in advance

[az38]

@Turka89 

you can use SELECTEDVALUE() inside FILTER() in measures statements

I m not sure I understand your logic but it could look like

Measure = 
CALCULATE(COUNTROWS(APPLICATION), ALLEXCEPT(APPLICATION, APPLICATION[Division]),  APPLICATION[CurrentStatus] = "ITI - Assessment")

[Turka89]

Hi @az38 ,

I have used your DAX with a bit of modification

Measure =
CALCULATE(COUNTROWS(APPLICATION), ALLEXCEPT(REQUISITION, REQUISITION[Division]),  APPLICATION[CurrentStatus] = "ITI - Assessment")

and it returns the total number of applicant on interview on all divisions and to all divisons. See picuture blow

Thank you 

[az38]

@Turka89 

so, how is your Division field connected with CurrentStatus? Is it in one table? If no, create a relationships between table