cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gaiusgw
Helper I
Helper I

Include results of 0 on visual

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: 

2.JPG

 

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. 1.JPG

 

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.

1 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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
lbendlin
Super User III
Super User III

"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.

@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.

2.JPG

 

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

Capture.JPG

 

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

3.jpg

 

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! 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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. 

 

Capture.JPG

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors