Forum Discussion

analyst31233's avatar
analyst31233
Regular Visitor
3 years ago

How do I create an IF > Countif calculation in DAX?

I have a very useful excel formula I use to check if a Company ID exists in another sheet with a value of "Yes", and if so, to return "Yes", but if not, to return "No". How can I create this exact same calculation in DAX?

 

The objective is to check if there is a Company ID, AND that the "Response" column is "Yes", because there can be multiple CompanyID

 

 

=IF(COUNTIFS(Vendor_List, [@[Company_ID]], Email_List, "Yes")>0,"Yes","No")

 

 

Company_Table:

 

 

Company_IDResponse of Yes?
123=IF(COUNTIFS(Vendor_List, [@[Company_ID]], Email_List, "Yes")>0,"Yes","No")

124

=IF(COUNTIFS(Vendor_List, [@[Company_ID]], Email_List, "Yes")>0,"Yes","No")

 

 

Email_Table:

 

EmailCompany_IDResponse
[email protected]123No
[email protected]123Yes
[email protected]124No
[email protected]124No
[email protected]124Yes
Need Help
Tutorial Requests

2 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    3 years ago

    analyst31233 

    is this what you want?

    Column = 
IF(MAXX(FILTER(Email,'Email'[Company_ID]='Table (2)'[Company ID]&&'Email'[Response]="Yes"),'Email'[Response])="","No","Yes")

    pls see the attachment below

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    This calculated column formula works

    Column = if(CALCULATE(COUNTROWS(Company),FILTER(Email,Email[Company_ID]=EARLIER(Company[Company_ID])&&Email[Response]="Yes"))>0,"Yes","No")

    Hope this helps.