Forum Discussion

sjhardy's avatar
sjhardy
New Member
4 years ago
Solved

Help with multiple counts with two variables

Hi all, I hope you can help.  I have a requirement to create two columns.  One column has a candidateID and the other column is clientreqno.  Both columns are set as text.  I want a column that will give me a count of the no of times a candidate id appears next to a requisition, and then another column that will give me the opposite for different reasons.  I have looked through other solutions on the forum but nothing is giving me the right value.  So as an example for CandidateID I would expect 5133447 to return a value of 2 as there are two distinct clientreqno.  and then I want another column that would give me for example clientreqno of 21000044 would have returned 8

 

I have this, but its giving me 0 as a value.  Can anyone help troubleshoot the dax:

Count =
VAR _clientno = 'Combined Candidate list'[ClientReqNo]
VAR _count =
COUNTROWS(
FILTER(
ALL('Combined Candidate list'),
_clientno='Combined Candidate list'[CandidateID]
)
)
RETURN
IF(
_count > 0,
_count,
0
)
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi sjhardy ,

     

    Please try:

    Distinct No each ID =
CALCULATE (
    DISTINCTCOUNT ( 'Combined Candidate list'[ClientReqNo] ),
    ALLEXCEPT ( 'Combined Candidate list', 'Combined Candidate list'[CandidateID] )
)
    Distinct ID each No = 
CALCULATE (
    COUNTROWS ( 'Combined Candidate list' ),
    ALLEXCEPT ( 'Combined Candidate list', 'Combined Candidate list'[ClientReqNo] )
)

    Output:

     

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

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    Write these 2 calculated column formulas

    =calculate(distinctcount(Data[ClientReqNo]),filter(Data,Data[CandidateID]=earlier(Data[CandidateID])))

    =calculate(countrows(Data),filter(Data,Data[CandidateID]=earlier(Data[CandidateID])))

    Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi sjhardy ,

     

    Please try:

    Distinct No each ID =
CALCULATE (
    DISTINCTCOUNT ( 'Combined Candidate list'[ClientReqNo] ),
    ALLEXCEPT ( 'Combined Candidate list', 'Combined Candidate list'[CandidateID] )
)
    Distinct ID each No = 
CALCULATE (
    COUNTROWS ( 'Combined Candidate list' ),
    ALLEXCEPT ( 'Combined Candidate list', 'Combined Candidate list'[ClientReqNo] )
)

    Output:

     

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