Forum Discussion
Why is DAX multiplying rows when removing filters with ALL?
A matrix visual will return a row for every line where the measure is not blank. Your ALL change the calculation to be the same as if you removed Manufacturer and Area from your visual. It would return something like
Name Measure A 6 B 5 C 11 Total 22 The measure amount is ignoring Manufacturer and Area so the measure returns an amount on all combinations. The join applies a filter, ALL removes the filter.
Super UserAdamMarczak I would stay away from using IF condition as it can be expensive and can slow down the DAX calculation. May be on smaller dataset it is not a big but complex/larger dataset, it can be costly.
parry2k understand but i wonder how to avoid mentioned scenario in this case. I need to drop filters from related table and calculate SUM over Name column. How do i check for blank so I don't get empty rows like here.
- parry2k
AdamMarczak the calculation I gave you will take care of it, did you tested it?
- MFelix
Hi AdamMarczak ,
When having tables related you need to understand that you don't need to make the use of filter to make the calculations, if you get the context from the correct table the use of the filters especcially ALL will only give you unexpected results.
In you case a simple measure like SUM and the use of the manufacturer name from the table will give you the correct calculation no need to make this complicated.
Regards,
MFelix
MFelix in my case I need to have manufacturer and area columns in my table so my context is too narrow so I need to drop some filters. I can't do this without ALL since my filter context is already A, Adam, 1 so my SUM will return 1 if I dont drop filter on Adam (Manufacturer) and 1 (Area).
parry2k I did, it kinda broke my mind but I guess it works really well because of how divide as IF BLANK return 1 otherise return blank. Clever. I have 600 to 2 TB models so I will test the performance. Clever trick indeed. Thanks!
