Include results of 0 on visual

[gaiusgw]

I have a table with product info (called inven) and a table with sales info (called opendet) that are linked by a many to many relationship using a custom column I made with [season][style][color]. Inven includes items that do not have sales on them yet. When I added the booked $s to the visual, anything that does not have a sale on the opendet table makes it so that the item is hidden. I would prefer it to reflect $0 rather than hide the line. 

Here are screenshots to hopefully make this a little clearer. 

Here you can see the full list from Inven table data: 

But once I add booked $ from opendet table, the visual filters down to the one item that has a sale rather than showing all items with zero dollars. 

I have tried changing the filter direction but am not getting the result I need. Not sure what I am missing here... 

Thanks in advance for advise.

[lbendlin]

"that are linked by a many to many relationship "  

this should be your first warning sign.  Many to many relationships introduce ambiguity. Either avoid  them by normalizing your data model a bit more, or only use single filter directions.

There are tricks like adding 0 to a measure that might produce BLANK() etc but these should be your last resort. Please provide sample data in usable format (not as a picture) and show the expected outcome.

[gaiusgw]

@lbendlin I had it as a many to many because of a duplicate error that I have now fixed so it is one to many now but I still get the same result actually. 

Here is a link to download 2 excel files; INVEN for all products and OPENDET for the sales amount. 

https://we.tl/t-qWqM5Tevl8

The relationship can be formed on "combo" column which combines season, style, color. 

Here is what visual looks like without adding in the sales info from OPENDET table.

Then when I add in Booked $ from OPENDET to visual above, it filters down to the one result that has a sale:

This is the result I would like to see (this is photoshopped):

Basically I do not want products with no sales to disappear. I would prefer they stayed there so I can see what has no sales. Or add $0.00 for anything that does not have a sale. 

My data is a daily import so it is always changing so I cannot simply add fake lines for products that have had zero sales. 

I hope this makes the scenario clear. If you need anymore details, just let me know and thats for the help! 

[Ashish_Mathur · Accepted solution]

Hi,

Do away with the Many to Many relationship.  With the help of Bridge Tables, ensure all relationships are Many to One and Single.  to your visual, drag as many fields as possible from the Dim Tables (Tables on one side of the relationship).  Try this measure

Measure 1 = coalesce([your measure],0)

Hope this helps.

[gaiusgw]

@Ashish_Mathur Thanks a lot! This did the trick 100%. Really appreicate you taking the time. 🙂 

[Ashish_Mathur]

You are welcome.

[gaiusgw]

@lbendlin one workaround I found is if you add a fake measure to the visual (like show = 1 - 1) , it will prevent lines with no sales from disappearing. It is not elegant but seems to get the job done.