Forum Discussion

L1102's avatar
L1102
Icon for Helper I rankHelper I
8 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...
  • FreemanZ's avatar
    FreemanZ
    8 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
Icon for Super User rankSuper User
8 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