Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Common values on Dynamic selection

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.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
    ERD
    5 years ago

    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!

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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_NameState_Name
      AppleFlorida
      OrangeFlorida
      BananaFlorida
      KiwiArizona
      BananaArizona
      MangoArizona

       

       

       

       

       
      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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's avatar
    ERD
    Community Champion
    5 years ago

    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!