Alternative to bidirectional relationship

[Anonymous]

So I have these table relationships as below.

Basically I want to filter to show Sales per Venue but I have no way of connecting Sales table to Venue table so it has to go through the Users table. Issue is that I have to make the relationship between Users and Calls table bidirectional otherwise it would show incorrect results. But the problem is that in my real report it is not an option for me to make the relationship bidirectional as it would conflict with other tables. Is there any DAX query I can use to filter Sales per Venue?

PBIX FILE

[CNENFRNL · Accepted solution]

The simplest applicaiton of expanded table,

Sales by Venue = CALCULATE(SUM(Sales[Sales]),Calls)

[jdbuchanan71]

@Anonymous 

You could also do it with a measure like this.

Sales by Venue = 
CALCULATE ( 
    SUM ( Sales[Sales] ),
    CROSSFILTER ( Calls[Username], Users[Username], BOTH )
)

This measure will also work for showing amounts by name for example.  It just makes the relationship between Calls and Users bi-directional when it is calculating rather than you having to set it that way in the model.

[amitchandak]

@Anonymous , Try a measure like

var _tab = summarize(allselected(Call), Call[username]) // or// summarize(filter(allselected(Call), Venue[Venue] in allselected(Venue[Venue])), Call[username])
return
calculate(sum(sales[sales]), filter(users,users[username] in _tab))

also treatas, check https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/