Common values on Dynamic selection

[Anonymous]

Dear Friends,

I have a serious doubt stopping me for days.I have spent enough time on forums but this is not answered anywhere.

I basically want to know - how to find the matching values (text) between two dynamic selection in a visual matrix or a table and then count the number of matching values.
------------------------------------------------
For example: I would like to know upon dynamically choosing ANY two Regional States out of 69 states, what are the matching products, in other words common products, in State 1 and State 2.

Products in state 1: Apple, Mango, Banana, Kiwi.

Products in state 2: Jackfruit, Cherry, Berry, Apple, Pineapple, Strawberry.
-----------------------------------------------------
I would like to know upon user choosing state 1 and state 2 as filter on a visual I want to the common product (Apple) to be shown and count the number of such common products. 

I believe it needs to be done by creating a measure something like;

--> CommonValues= IF( Values(State1) IN {Values(State2)},"Pull out that common value and count the number of common value", 0).
-----------------------------------------------
I would really be grateful if anyone can help me out of this.

Thank you very much in advance.

[Anonymous · Accepted solution]

Measure 

CommonFruit = 
VAR NumberOfSelectedStates = COUNTROWS(ALLSELECTED(FruitsAndStates[State_Name]))
VAR FruitCount = COUNTROWS(FruitsAndStates)
VAR CommonCheck = IF(NumberOfSelectedStates=FruitCount,1,BLANK())
RETURN
CommonCheck

Filter on Visual using the measure

As always, there are many ways to do it. That's why I asked about your table structure. Hope this helps. Other than displaying the common fruits, if you want to count them, you could add another measure similar to this one and count the number of common fruits.

[ERD · Accepted solution]

Hello @Anonymous ,

Supposing states in your table might have different amount of common products and each state might have several occurancies of the same product. Also you might want to choose > 2 states..
Here are 3 measures that take into account the scenario above:

#1:
CommonStatePerFruitCount = 
CALCULATE(
    DISTINCTCOUNT('Common values on Dynamic selection'[State]),
    ALLSELECTED('Common values on Dynamic selection'),
    VALUES('Common values on Dynamic selection'[Fruit])
)

#2:
CommonFruitCount = 
VAR chosenStatesAmt = COUNTROWS(ALLSELECTED('Common values on Dynamic selection'[State]))
RETURN
IF(chosenStatesAmt > 1,
CALCULATE(
    DISTINCTCOUNT('Common values on Dynamic selection'[Fruit]),
    FILTER(
        ALLSELECTED('Common values on Dynamic selection'[State],'Common values on Dynamic selection'[Fruit]), 
        [CommonStatePerFruitCount] = chosenStatesAmt)
))

#3:
CommonFruitCheck = 
VAR chosenStatesAmt = COUNTROWS(ALLSELECTED('Common values on Dynamic selection'[State]))
RETURN
IF( [CommonStatePerFruitCount] = chosenStatesAmt, 1, BLANK())

Use CommonFruitCount as values in your Treemap and CommonFruitCheck for filtering the table with common products (see image below).

Did I answer your question? Mark my post as a solution!

[Anonymous]

It is possible. Please provide your table structure with field names.

[Anonymous]

Thank you Sreenath. Please see table below.  I want PBI visual to show common fruits, Banana, upon selecting Florida and Arizona. But as you can see from the visual posted below the table it shows all the fruits in both states..

Fruit_Name	State_Name
Apple	Florida
Orange	Florida
Banana	Florida
Kiwi	Arizona
Banana	Arizona
Mango	Arizona

 
Selecting two states from the treemap, should show onlyy the common fruit Banana on the left side table.

I would really appreciate your help.

Thank you.

[Anonymous · Accepted solution]

Measure 

CommonFruit = 
VAR NumberOfSelectedStates = COUNTROWS(ALLSELECTED(FruitsAndStates[State_Name]))
VAR FruitCount = COUNTROWS(FruitsAndStates)
VAR CommonCheck = IF(NumberOfSelectedStates=FruitCount,1,BLANK())
RETURN
CommonCheck

Filter on Visual using the measure

As always, there are many ways to do it. That's why I asked about your table structure. Hope this helps. Other than displaying the common fruits, if you want to count them, you could add another measure similar to this one and count the number of common fruits.