DAX code to check multiple selected values in a Treemap

user_8238772 0
Viewed 942

I am working on dynamically changing titles in PowerBI. I have Treemap which displays the population of 6 US states - 2 states from the east (NY, MA) and 3 from the west (CA, OR, WA).

The treemap looks like this -

enter image description here

A user can choose multiple states from a treemap by pressing ctrl and clicking on the square that represents the state. When the user selects both the eastern states, I want the title to say "population in the east states" and similarly for the west too. Can this be done using DAX?

My title requirements -

  1. If selected states are MA && NY then display "Population in the East States" as the title.
  2. If selected states are CA && OR && WA then display "Population in the West States" as the title.
  3. If only 1 state is selected then display the name of the state in the title.

I could do 3. Can someone please help me with 1 and 2?

dax powerbi
1 Answers

Here's a measure that will do this.

Title = 
SWITCH( TRUE(),
    CONCATENATEX(VALUES(Table3[State]), Table3[State], ",") = "MA,NY",
    "Population in the East States",
    CONCATENATEX(VALUES(Table3[State]), Table3[State], ",") = "CA,OR,WA",
    "Population in the West States",
    HASONEVALUE(Table3[State]),
    VALUES(Table3[State]),
    "Population by State"
)

The VALUES function returns a list of the distinct values in the column specified.

user_7652260
Related