Forum Discussion
Alternative to bidirectional relationship
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?
The simplest applicaiton of expanded table,
Sales by Venue = CALCULATE(SUM(Sales[Sales]),Calls)
3 Replies
CNENFRNLCommunity Champion
The simplest applicaiton of expanded table,
Sales by Venue = CALCULATE(SUM(Sales[Sales]),Calls)- jdbuchanan71
Super User
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/
