Forum Discussion

qwaszx55's avatar
qwaszx55
Frequent Visitor
8 years ago
Solved

Filter using OR from 2 different tables.

I'm sure this is much easier than I'm experiencing but its crushing me at the moment.

 

I have a service contracts table with say "CompanyName,ContractNo,Status" and a servicecontractsitem table that has "Item,Enddate,EndReason"

 

All I want to do is filter the CompanyName column on a single report with "ServiceContract[Status]" = "Terminated" OR ServiceContractItem[EndReason] = "".

 

If it was AND I could just drag them to the report visual filters but it seems I have to write this one. 

 

Why in the world I can't figure out this simple filtering is beyond me but I've looked everywhere. I've tried calculated columns with a nested if statement and realized that must have been one of the ugliest ways to do it.

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    qwaszx55,

    In DirectQuery mode, create calculated columns using BILASolution's DAX in Report View, you can also get expected result.


    Regards,
    Lydia

4 Replies

  • BILASolution's avatar
    BILASolution
    Solution Specialist
    8 years ago

    Hi qwaszx55

     

    Try this...

     

    First, create 2 calculated columns

     

    Status = RELATED('Service Contract'[Status]) 
    Status or Reason = IF(OR('Service Contract Item'[EndReason] = "";'Service Contract Item'[Status] = "Terminated");"Yes";"No")

     

     

    Second, use "Status or Reason" calculated column to filter.

     

     

     

    I hope this helps

     

    Regards

    BILASolution

    • qwaszx55's avatar
      qwaszx55
      Frequent Visitor
      8 years ago

      How do you go about this in direct query mode? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        qwaszx55,

        In DirectQuery mode, create calculated columns using BILASolution's DAX in Report View, you can also get expected result.


        Regards,
        Lydia