Forum Discussion
DAX Measure evaulation context issue with related table
Hello all,
I think, after building a "clean" test model I've discovered the issue at hand. I have a many-to-many relationship which is causing the issue I'm seeing. My clean model has only many-to-single relationships. This would seem to make sense to me based on what I'm seeing. Based on what I've described does this make sense to everyone?
Community ChampionA visual of your model would help. I may have missed it but didn't see how you are relating Transactions to the rest of the model, but I suspect it is not filtering the rest of the model. Meaning, whatever you are relating Transactions to, Transactions is the Many of a One to Many.
If that is correct, see what happens if you modify this measure:
Distributed Cost = [Sum of Transactions] * [Hrs % of GT]
Change it to this:
Distributed Cost =
CALCULATE(
[Sum of Transactions] * [Hrs % of GT],
CROSSFILTER ( Transactions[SomeField], TheOtherTable[KeyField], BOTH )
)What CROSSFILTER() does inside of CALCULATE is turns a single directional filter to bi-directional for that measure only, which is what you want. You can turn on bi-directional in the relationship itself, but that should be avoided at all costs as it can introduce unexpected issues or simply break things as the model gets more complex.
If that is not helpful, please post a pic of your model from the model view and how Transactions is related to another table.
Hi,
Thanks for the quick response. At the moment there is no relationship between the Transactions table and the Usage and other associated tables. The reason being I brought the Transactions table in to explicitly slice down to a single transaction, and use its value as a constant for the distirbution calculations. In the future I might want to slice to a new transaction and then re-calculate a new disitrubtion based on new usage data.
I could create a relationship between the Usage data and the Transactions based on "Product" if you think that might help? I assumed that since I was basically using the value as a constant a relationship wasn't neccessary, but perhaps it is?
- edhans
Anonymous wrote:Hi,
Thanks for the quick response. At the moment there is no relationship between the Transactions table and the Usage and other associated tables. The reason being I brought the Transactions table in to explicitly slice down to a single transaction, and use its value as a constant for the distirbution calculations. In the future I might want to slice to a new transaction and then re-calculate a new disitrubtion based on new usage data.
I could create a relationship between the Usage data and the Transactions based on "Product" if you think that might help? I assumed that since I was basically using the value as a constant a relationship wasn't neccessary, but perhaps it is?
Then I'm confused. You cannot filter anything with a disconnected table via a slicer, and to use it with measures you have to use measures like TREATAS() to create a virtual relationship. Again, I'd need to see a more compressive model of what you have and are trying to accomplish.
- amitchandak
Super User
You can filter when tables are not connected. But grouping data across the table and then provide data at a different level will require lots of stuff
