Identifying a Product gap based off 2 data tables

[L1102]

Hello,

Hoping for some guidance. My set up is as per below.

Table 1: Sales Data Per Location Per Product

Table 2: Master Location List <-- Does not go down to the product level

Relationship between the tables : Location Name (1:Many)

Objective: Looking to build a table that once a product is select a lists will populate with all locations the product is not selling in.

My original thought was to set up a distinct count for both tables, build a table visual using table 2 location information, and filter against the distinct count measures by filtering in only counts that are blank.

It works perfectly on all location levels until I use any filter product related. Once I select the prodcut I want to evaluate, the table will only populate the stores that have sales against it. Do I need a different DAX rather than a simple distinct count?

I don't know if i'm making this too complicated, but any advice would be great!

Thanks in advance.

[cengizhanarslan]

1. Create a Product dimension (distinct list of products) and relate it to Sales.

DimLocation[Location] (1) → Sales[Location] (*)

DimProduct[Product] (1) → Sales[Product] (*)

Then use slicers from dimensions, not from the Sales fact.

2. Put DimLocation[Location] in a table visual, then add this measure:

Has Sales :=
IF ( CALCULATE ( COUNTROWS ( Sales ) ) > 0, 1, 0 )

Now filter the visual: Has Sales = 0

[FreemanZ · Accepted solution]

hi @L1102 ,

Not sure if i fully get you, supposing you have two tables like below:

and they are related on location column as many to 1.

Try the following:

1) plot a slicer with sales[product] 
2) plot a table visual with location[Location] 

3) write a measure like below:

FilterMeasure = 
VAR _count = DISTINCTCOUNT(Sales[product])
VAR _result = IF (_count>0, 1, 0) 
RETURN _result

4) pull the measure to the filter pan of the table visual and select 0.

it worked like:

Please find more info in the attached file.

[L1102]

Thank you so much for this!!

[lbendlin]

To report on things that are not there you need to use disconnected tables and/or crossjoins.

Your product filter needs to come from such a disconnected table.  Then use a measure as a visual filter to include only the rows that do not satisfy the filter choice.

[L1102]

@lbendlin my data set is pretty massive, I tried this and Power BI have been sitting at "working on it" for a while.

Hoping there is some other work around someone can magically conjure up.