Forum Discussion

L1102's avatar
L1102
Helper I
7 months ago
Solved

Identifying a Product gap based off 2 data tables

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.

  • FreemanZ's avatar
    FreemanZ
    7 months ago

    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.

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    7 months ago

    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's avatar
      L1102
      Helper I
      7 months ago

      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.

  • FreemanZ's avatar
    FreemanZ
    Super User
    7 months ago

    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.

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    7 months ago
    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