Keep rows with unmatching data when filtering

[Anonymous]

Hi!

I recently started exploring the capabilities of PBI, and have run into a scenario that is pretty straight forward, but where I can't seem to find a straight forward solution.

Let's say I have two tables in this simplified version.

Table 1 contains sales targets based on year, country and product on the form below. Targets are only based on year, country and product!

YEAR | COUNTRY | PRODUCT | SALES_TARGET

2015       Spain         Bikes             100

2015       Spain         Socks            200

2016       Spain         Bikes             150

2016       Spain         Socks            250

Table 2 contains sales data where each row represents one item being sold (Bike in this case), but it also shows some additional attributes on the product, Let's say we have the following data available. No sales exists on colour Yellow though!

YEAR | COUNTRY | PRODUCT | COLOUR

2015      Spain           Bikes          Red

2015      Spain           Bikes          Blue

To connect the targets with the sales I have created a key in both tables based on YEAR, COUNTRY and PRODUCT, so I have a one-to-many relationship between them.

I have successfully managed to create a dashboard summarizing sales and comparing them to the targets on the following format:

YEAR | COUNTRY | PRODUCT | TARGET | SALES

2015      Spain           Bikes          100         95

2016      Spain           Bikes          150         155

So far so good, but now I run into trouble. When I add a slicer, to filter out sales based on the COLOUR attribute, everything works fine as long as I have sales that match my selection. If no sales are matching then I  also lose my target values.

For example If i select RED or BLUE, all data is shown correctly as I have sales on those colours, but If I select Yellow I lose all data for Spain, including the targets that should remain the same.

What would like to achieve is to be able to filter on colour Yellow, and still see the targets for the different years, and just see 0 or even null as sales. The targets are still the same, and are not based on colour, I just want the actual sales data to be affected by my colour slicer.

I'm sure this must be possible? What would you reccomend for a solution?

I've tried different relationships, I've tried merging tables and I've tried creating a new table with the NATURALLEFTOUTERJOIN function. They all fail on the filtering issue.

Thanks in advance!

[TomMartens · Accepted solution]

I'm wondering how you would select the color yellow, if the color is just in your sales-table, but no yellow bikes have been sold sofar.

I personally would no relate the sales and the targets table, but would use separate "dimension" tables like "product" and "year", then you can relate these tables to your "fact" tables: sales and saltestargets. Then you can use the color column from the sales-facts to slice your sales data.

Please be aware, that in my example the direction "Cross filter direction" of the relationships should be set to "Single".

Hope this helps

[TomMartens · Accepted solution]

I'm wondering how you would select the color yellow, if the color is just in your sales-table, but no yellow bikes have been sold sofar.

I personally would no relate the sales and the targets table, but would use separate "dimension" tables like "product" and "year", then you can relate these tables to your "fact" tables: sales and saltestargets. Then you can use the color column from the sales-facts to slice your sales data.

Please be aware, that in my example the direction "Cross filter direction" of the relationships should be set to "Single".

Hope this helps

[Anonymous]

Hi Tom!

Thanks for the advice, will look into it and let you know how it went. Hopefully I get time to do it this week, not sure though. I'll make sure to mark this answer afterwards. 

Regarding the yellow bikes i simplified the truth a little bit. Like you say they must exist to be able to filter them. Translated to this scanario, thare are actually yellow bikes sold, but not in Spain for these years. Other countries might have them 🙂

[ImkeF · Accepted solution]

Agree with Tom. This link might be helpful: http://exceleratorbi.com.au/multiple-data-tables-power-pivot/

[Anonymous]

Hi Imke!

Thanks for the link, will have a look at it.