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...
  • 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.

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.

  • L1102's avatar
    L1102
    Helper I
    7 months ago

    Thank you so much for this!!