COALESCE removes my filters

[MCornish]

Hi All

Im using the COALESCE function (same problem happens with an IF) to return a 0 when a measure is blank. Without the COALESCE the measure works fine.

I have a table that lists customers which is filtered by salesperson. If I add the measure to the table, it filters the table to only those customers where the measure is > blank. I selected Show Items With No Data and now I see all filtered customers with blanks.

The COALESCE was used to swap this blank out for a 0. However when I add the measure to the table it lists every customer in the raw table and completely ignores my filter. Adding the salesperson into the table shows the selected salesperson against every customer which is incorrect.

Without COALESCE:

With COALESCE:

See how the customer number is now every customer.

Any help would be greatly received

[Syndicate_Admin]

Good day, I have the same problem with a database and I can't quite understand the solution of this case. Could you help me please?

Thank you, greetings

[amitchandak]

@MCornish , +0 , coalesce all those force a left join with the table and this shows all other values.

[MCornish]

While yes I agree it forces a left join, it should still respect the filters placed on the customer table

[MCornish · Accepted solution]

Ok. I found the issue. 

Issues was in the relationship between two other tables (it's a big model)