cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Filter on record existing in a related table

I have 2 tables: many Stages to each Request. Both tables coming from a SQL database.

In Power BI, I want to allow easy filtering of all the Requests which have a Stage of type 'ABC'.

 

This is the logic I would use in SQL:

select Requests.RequestId
from Requests
where RequestStatus = 'Active'
and exists (select *
                  from Stages
                  where Requests.RequestId = Stages.RequestID
                  and Stages.StageName = 'ABC')

 

The SQL above returns the Request records I'm interested in.

 

My idea is to add a new column to the Request table in Power BI, and allow filtering on that new column.

But I can't work out the DAX syntax for my new column.

My two tables are joined/related in Power BI.

 

Thanks for any help Smiley Happy

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I found one way to solve this. Posting my answer in case it might help someone else ...

 

My new column on the Requests table:

 

Request Has ABC Stage = COUNTROWS(FILTER(RELATEDTABLE(Stages),Stages[StageName]="ABC"))>0

 

This gives a nice True/False value at Request level, for use in filters.

 

Any alternative answers would be gratefully received too Smiley Happy

 

View solution in original post

1 REPLY 1
Anonymous
Not applicable

I found one way to solve this. Posting my answer in case it might help someone else ...

 

My new column on the Requests table:

 

Request Has ABC Stage = COUNTROWS(FILTER(RELATEDTABLE(Stages),Stages[StageName]="ABC"))>0

 

This gives a nice True/False value at Request level, for use in filters.

 

Any alternative answers would be gratefully received too Smiley Happy

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors