cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ramshoney1225
Helper IV
Helper IV

How to write Filter condition on with two different tables ?

Hi,

 

I want to write a DAX, i have two tables Tablea and tableb both are joined

i need to count and count distnct measure

 

COUNTID = count(id) based on tablea.id = tableb.id and tablea.type = "Protect"

 

I need to get count based on the two conditions in DAX

table( id )= tableb ( id ) and Tablea.type = "Protect" on

 

Thanks

Rams

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@ramshoney1225 , Assume both tables has a relation ID column

Try like

calculate(count(tablea[ID]), tablea[type] = "Protect", not(isblank(tableb[ID])))

or

calculate(count(tablea[ID]),filter(tablea, tablea[type] = "Protect" && tablea[ID]= related(tableb[ID])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Thanks for the update,

 

I'm able to use the 2nd one , i didnt understand the first one.

 

If i dont need any hard code filter in that can i use as below right ?

 

calculate(
count('tablea'[ID]),
FILTER('tablea ,'tablea'[ID] = related('tableb'[ID]))       --- Where i have relation between two tables 
)
 
Thanks
Rams

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@ramshoney1225 , Assume both tables has a relation ID column

Try like

calculate(count(tablea[ID]), tablea[type] = "Protect", not(isblank(tableb[ID])))

or

calculate(count(tablea[ID]),filter(tablea, tablea[type] = "Protect" && tablea[ID]= related(tableb[ID])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Thanks for the update,

 

I'm able to use the 2nd one , i didnt understand the first one.

 

If i dont need any hard code filter in that can i use as below right ?

 

calculate(
count('tablea'[ID]),
FILTER('tablea ,'tablea'[ID] = related('tableb'[ID]))       --- Where i have relation between two tables 
)
 
Thanks
Rams

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!