Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Canopy
New Member

Problem with measure to get results depending other table

Hi everyone!

I have a couple of measures in my dashboard:

DAX 1

Num Of eRetailers =
VAR ActualRetailersCALCULATE(   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 SearchTermSelectedCALCULATE(  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!
 
 
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors