Forum Discussion
Common values on Dynamic selection
- Anonymous5 years ago
Measure
CommonFruit = VAR NumberOfSelectedStates = COUNTROWS(ALLSELECTED(FruitsAndStates[State_Name])) VAR FruitCount = COUNTROWS(FruitsAndStates) VAR CommonCheck = IF(NumberOfSelectedStates=FruitCount,1,BLANK()) RETURN CommonCheckFilter 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.
- 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!
It is possible. Please provide your table structure with field names.
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.