Filter using OR from 2 different tables.

[qwaszx55]

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.

[Ashish_Mathur]

Hi,

We can merge two tables into one and then filter the dataset.

[qwaszx55]

How do you go about this in direct query mode? 

[Anonymous · Accepted solution]

@qwaszx55,

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


Regards,
Lydia

[BILASolution]

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