Problem with measure to get results depending other table

[Anonymous]

Hi everyone!

I have a couple of measures in my dashboard:

DAX 1

Num Of eRetailers =

VAR ActualRetailers = CALCULATE(   DISTINCTCOUNT(data[idStore]),    ALL(Data[Date]) )

RETURN  ActualRetailers

DAX 2

Term score = 

VAR MaxDate = CALCULATE(Max(data[Date]))

VAR Score      = CALCULATE( SUM(Data[weighted_score]) / [Num Of eRetailers],    data[Date] = MaxDate && Data[rank_type] = 1  )

VAR SearchTermSelected = CALCULATE(  DISTINCTCOUNT('SearchTerms'[idSearchTerm]),  data[Date] = MaxDate)

VAR Ratio = DIVIDE(Score, SearchTermSelected)

Return Ratio

I need to add in DAX 2 a rule because in the model I have a table with the idSearchTerm that Not Apply to a specific Retailer, the table is : Rules (idSearchTerm, idRetailer) ,

my question is, how to add this logic to my DAX 2 in the part of VAR SearchTermSelected to exclude a combination of Term and Retailer?.

For example: I have Sales for many retailers, but, the Term 4 is not sold in Retailer 5, this row is present in the Rules table (Term 4, Retailer 5), how not include this Term / Retailer combination? I just need to get the valid terms, the terms not presents in the rule table.

Sorry for my long explanation! , If more details are needed just Tell me!

Thanks Team!

[lbendlin · Accepted solution]

Filters in CALCULATE() are applied as a combination ( "AND") unless you specifically use the "||"  symbol for "OR"  alternatives.