Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 = ...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Anonymous , 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])))

  • Anonymous's avatar
    Anonymous
    6 years ago

    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
amitchandak's avatar
amitchandak
Super User
6 years ago

Anonymous , 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])))

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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