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!
