Counting only if value of another column is met

[andrew260z]

I have 2 tables (Positions) and (Candidates) linked on DemandID, with a relationship one to many towards Candidates. I am trying to tally up the number of positions that are "closed" meaning the position is recruited (StatusID = 7) and how many are open (StatusID <> 7) however because of one to many I need to somehow figure out that the best (lowest) status ID of the candidate is and store that as being an OpenPos. Ignoring the other status(es) and counting only towards it being open. I also have situations where the statusID = 7 and there might have been a statusID = 8 and I'm counting that as Open by mistake. 

What I need to have is in the instance where one of the statusID is 7 the position would be recruited so not counting it as open for any other status

DemandID	StatusID	Occurence	OpenPos
179	7	2	0
179	8	2	0

OR if there is no statusID of 7 then I only want to count the openPos as the lower of the 2 numbers

DemandID	StatusID	Occurence	OpenPos
179	4	2	1
179	8	2	0

Attached is what I've currently got going on.

[d_gosbell]

I don't think you need to store anything in a calculated column if all you want to do is get a count of the Open or Recruited positions you should be able to do this with measures like the following:

Recruited = SUMX(
    Positions,
   IF( COUNTROWS( CALCULATETABLE( RELATEDTABLE( Candidates ),Candidates[StatusID] = 7 )) > 0 , 1)
)

Open = SUMX(
    Positions, 
    IF( COUNTROWS( CALCULATETABLE( RELATEDTABLE( Candidates ) ,Candidates[StatusID] = 7 ) ) = 0 , 1)

)

Basically these measures just loop over all the positions in the current filter context and count the related rows from Candidates where the StatusID = 7 if there are 0 rows meeting this condition we count that position as open, if there are 1 or more with a status of 7 the position is counted as recruited.

[andrew260z]

Thanks for the reply I think it would help but the problem is I cannot run the relatedtable function (the statusID is a calculated column) so it doesn't allow me to enter that in the formula to create the measure. 

If I can somehow get the OpenPos to be 0 where the demandID matches and the occuence is >1 on the lowest value of the BestStatusIfMultiple that would be what I'm trying to achieve.

[d_gosbell · Accepted solution]

It should not matter if the StatusID is a calculated column, the measure I posted should still work

This screenshot is from a simple mock-up I just built using the description you provided of your data model. I've attached the pbix file to this reply so you can see it working and maybe figure out where it differs from your model.

[andrew260z]

thanks for the great example and explanation. I've been able to get this to work now, I foolishly was not creating a measure (instead was trying to do a calculated column) that was why it was not working for me. (for the SUMX DAX) - all looks like it adds up correctly on my report. - I'm off to try learn more DAX and understand these statements a little better I think!