Forum Discussion
DAX help
Make sure the relationship is setup as "both" not "single" under Cross Direction Fitler. I was able to get it to work with a model like this:
Actually, I'm not sure the figures you have are meaningful. Your sales table has an outlet indicator (A,B,C), but no fuel type indicator. Because each outlet sells multiple types of fuel, it's impossible to attribute any sale to a specific fuel type.
The reason you see $4,265 for Diesel and Gasoline and $3,266 for Kerosene are because Diesel and Gasoline are sold at A,B, and C where Kerosene is only sold at A and C.
What happens is the FuelType table is filtered by fuel type, and that filter carries forward through the bi-directional filter to the Sales table.
The Sales table can only be filtered by the outlet type (A,B,C). Therefore, for Kerosene, the outlets of A and C are carried forward to the Sales table and the amount is summed and returned.
Hi Anonymous
I tried again it worked. Actually we need to do the direction setting to "Both" for both the relation. Thanks for the suggestion.
Could you please tell me the rationale for this
Actually, I'm not sure the figures you have are meaningful. Your sales table has an outlet indicator (A,B,C), but no fuel type indicator. Because each outlet sells multiple types of fuel, it's impossible to attribute any sale to a specific fuel type.
The reason you see $4,265 for Diesel and Gasoline and $3,266 for Kerosene are because Diesel and Gasoline are sold at A,B, and C where Kerosene is only sold at A and C.
What happens is the FuelType table is filtered by fuel type, and that filter carries forward through the bi-directional filter to the Sales table.
The Sales table can only be filtered by the outlet type (A,B,C). Therefore, for Kerosene, the outlets of A and C are carried forward to the Sales table and the amount is summed and returned.
I know it doesnt make sense as you have correctly pointed out. What I actually trying to do is
1) There are many other fuels that are sold at the outlets. Some generate high revenue some generate low revenue
2) I am going to use a word cloud visual just to highlight which all products are solds. The size of each visual will depend upon revenue generated. As in this case Gasoline and Diesel will be shown bigger compared to kerosene
Hope it helps