Our usual Measures aggregate the Values from columns in one Fact table. But what is needed to calculate a result that depends on Data in another Fact table? Let’s look at a possible solution.
What is needed to calculate a result based on a specific value in DAX?
Simple: A filter.
But what if the filter has to be a table?
Fortunately, I already know how to work with tables in DAX Measures:
And now, my client gave me a challenge to put my knowledge to the test:
Look at the following Report:
As you can see, the number of products sold differs between Online and Retail Sales.
My client asked the following questions:
- What is the Online Sales Amount for the Products sold in Retail Stores as well?
- And which Products are sold only Online?
Can I answer the first question in Power BI by applying a Filter on the left table like this?
Unfortunately, this doesn’t work.
The reason is that no Relationship connects these two tables, and the Filter direction to Dimension tables works only in one direction.
Yes, I could change it to bidirectional filtering. But this can introduce issues as it will not work in any situation.