Forum Discussion

manish_tripathi's avatar
manish_tripathi
Frequent Visitor
3 years ago
Solved

Count with multiple conditions

I have a table where i have User email (as text), Bidstatus (as text) and Bid created date (in Datetime format)

 

where i am counting the interviewed talent using the following measure

 

Interviewed Talent =
CALCULATE(
    COUNTA('Bids made by MT'[BidStatusName]),
    'Bids made by MT'[BidStatusName] = "Back Out" ||
    'Bids made by MT'[BidStatusName] = "Credentialing" ||
    'Bids made by MT'[BidStatusName] = "Interview Rejected" ||
    'Bids made by MT'[BidStatusName] = "Interview Pending"||
    'Bids made by MT'[BidStatusName] = "Interview Scheduled"||
    'Bids made by MT'[BidStatusName] = "Customer Interview"||
    'Bids made by MT'[BidStatusName] = "On Billing"||
    'Bids made by MT'[BidStatusName] = "Drop" 
)
 
Now only for BidstatusName as "Drop" i want to count only those where bid date was prior to "nov 1, 2022" and count rest as they are
 
I tried modifying the DAX to following but it is not working:
 
Interviewed Talent =
CALCULATE(
    COUNTA('Bids made by HEB'[BidStatusName]),
    'Bids made by HEB'[BidStatusName] = "Back Out" ||
    'Bids made by HEB'[BidStatusName] = "Credentialing" ||
    'Bids made by HEB'[BidStatusName] = "Interview Rejected" ||
    'Bids made by HEB'[BidStatusName] = "Interview Pending"||
    'Bids made by HEB'[BidStatusName] = "Interview Scheduled"||
    'Bids made by HEB'[BidStatusName] = "Customer Interview"||
    'Bids made by HEB'[BidStatusName] = "On Billing"||
    ('Bids made by HEB'[BidStatusName] = "Drop" && 'Bids made by HEB'[Created]<"2022-11-01")
)
 
can you please suggest a possible solution
Need Help
Tips and Tricks
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    3 years ago

    manish_tripathi , Try like

     

    Interviewed Talent =
    CALCULATE(
    COUNTA('Bids made by HEB'[BidStatusName]),filter('Bids made by HEB' ,
    'Bids made by HEB'[BidStatusName] in { "Back Out", "Credentialing","Interview Rejected" ,"Interview Pending", "Interview Scheduled", "Customer Interview", "On Billing", "Drop" }&& 'Bids made by HEB'[Created]< date(2022,11,01) )
    )

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    manish_tripathi , Try like

     

    Interviewed Talent =
    CALCULATE(
    COUNTA('Bids made by HEB'[BidStatusName]),filter('Bids made by HEB' ,
    'Bids made by HEB'[BidStatusName] in { "Back Out", "Credentialing","Interview Rejected" ,"Interview Pending", "Interview Scheduled", "Customer Interview", "On Billing", "Drop" }&& 'Bids made by HEB'[Created]< date(2022,11,01) )
    )

  • manish_tripathi's avatar
    manish_tripathi
    Frequent Visitor
    3 years ago

    Thank you amitchandak however the second condition i want to apply only for drop status and not the rest, is there a way to do that as well