Show exceptions in table visual

[holodan95]

Hello All,

I'm currently facing a problem with showing only those products that have no stock, or never been to a shop.

What I have:

Table 'A' - A table with the products, that need to be in a shop (all distinct)

Table 'B' - A table with all the stock data with: /date/shop/product/stock (many rows are duplicates which I cannot change)

In Table 'B', the SQL database automatically generates a row with 0 value, if a product that previously been to the shop is not in stock, however it doesn't, if said product have never been to a specific shop. If the product is in stock, it will generate a value accordingly.

From these two, I should be able to show only those products, that have 0 quantity in stock, or never been registered in the shop.

I can only establish a 1:Many relationship between table 'A' and 'B'

Example:

Table 'A'

Product name	Product code
AAA	XXX0089
BBB	XXX0090
CCC	XXX0091

Table 'B'

Date	Shop code	Product name	Stock quantity
1	S1	AAA	0
1	S1	BBB	1
1	S2	AAA	1
1	S2	BBB	0
2	S1	AAA	1
2	S1	BBB	0
2	S2	AAA	0
2	S2	BBB	1

What I need is a visual table, that gives me back the following, whith these slicers:

Date: 2

Shop code: S2

The expected table:

Product name	Stock
AAA	0
CCC	0

As you can see, I have product AAA with the 0 amount, and also CCC which has never been to the shop (it will not have a generated line in Table 'B')

Is there a way to make this happen? I could already make a table which shows all the products that have been to the shop, with the current stock amount, and on another, all the products possible. However I need the exact opposite. Products with NO value, or which were never in the shop.

Thanks for the help!

[Greg_Deckler · Accepted solution]

@holodan95 Either https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534

Or, Stock Measure = SUM('Table B'[Stock quantity]) + 0

Filter for only 0's for that measure.

[holodan95]

@Greg_DecklerThank you! I can't believe how simple the solution was. 

"Stock Measure = SUM('Table B'[Stock quantity]) + 0" actually worked.

Thanks again!