Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
qwaszx55
Frequent Visitor

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.

1 ACCEPTED SOLUTION

@qwaszx55,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

How do you go about this in direct query mode? 

@qwaszx55,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BILASolution
Solution Specialist
Solution Specialist

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") 

tabla.png

 

 

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

 

resultado.png

 

 

I hope this helps

 

Regards

BILASolution

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.