Forum Discussion

jas_power's avatar
jas_power
Icon for Helper I rankHelper I
3 years ago
Solved

Determine Common Value Between Selected Items

Is there a way to write a measure to replicate the total column in a matrix? I need to find, of all the items selected from a slicer, what is common between them according to another variable in another table. 

 

I have a slicer of products that come from a product dimension table.

 

I want to have a geography slicer based off my geography dimesnion table that needs to be filtered by the selected items, but ony show geographies when all of the selected items in the product slicer share the same geography.

 

I played around and noticed a matrix provides the value I'm looking for, but I am unsure how to write it into a measure.

 

I've created a measure that tells me how many items are selected from the product slicer:

 

countOfSelected = 
IF(ISFILTERED(prod_map[prodNameBase]), COUNTROWS(VALUES(prod_map[prodNameBase])),0)

 

 

I was thinking the logic would be if the value from countOfSelected is equal to the total value from the matrix, then it should be included in the geography slicer. In this scenario, the countOfSelected = 5 so I would expect only Canada to be available in the geography slicer because all of the selected items have a value for Canada.

 

Does anyone know how to determine a common value for all selected items?

 

Need Help
  • jas_power's avatar
    jas_power
    3 years ago

    I figured it out. I created a measure that I can use as a filter where geographyFilter = 1 on my geography slicer.

     

    geographyFilter = 
var countofSelected = IF(ISFILTERED(prod_map[prodNameBase]), COUNTROWS(VALUES(prod_map[prodNameBase])),0)
var countofGeography = CALCULATE(COUNT(addGeography[geoId]),ALLSELECTED('prod_map'[prodNameBase]))
return
IF(countofSelected = countofGeography, 1, 0)

     

    Where prod_name_base is the variable that feeds my Product slicer and geoId is the id that links to my geography dimension table. 

     

    Here is the result - I left the matrix in just to show that it's working:

    I hope this may help someone in the future. 

     

1 Reply

  • jas_power's avatar
    jas_power
    Icon for Helper I rankHelper I
    3 years ago

    I figured it out. I created a measure that I can use as a filter where geographyFilter = 1 on my geography slicer.

     

    geographyFilter = 
var countofSelected = IF(ISFILTERED(prod_map[prodNameBase]), COUNTROWS(VALUES(prod_map[prodNameBase])),0)
var countofGeography = CALCULATE(COUNT(addGeography[geoId]),ALLSELECTED('prod_map'[prodNameBase]))
return
IF(countofSelected = countofGeography, 1, 0)

     

    Where prod_name_base is the variable that feeds my Product slicer and geoId is the id that links to my geography dimension table. 

     

    Here is the result - I left the matrix in just to show that it's working:

    I hope this may help someone in the future.