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) bet...
  • 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
    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!