When we want to be sure that we made the right selection 💡
Have you ever found yourself in a situation where you sliced and diced your report, drew some insights, but when it comes to present or share those findings with others you had to double-check that you selected the right stuff?
I cannot tell you how many times I had to double-check the slicer selection to make sure that I have done the right thing.
To solve the above problem and to help my report consumers I usually like to highlight one way or another the slicer selection.
Options and DAX Codes
Let’s start with a simple SELECTEDVALUE:
SELECTEDVALUE ( Customers[Customer Name 1] )
Secondly, we can create a 0-1 kind of measure. Meaning that it’s either going to show a single selection or flag that users have selected multiple elements:
SWITCH (
TRUE (),
//if Customer Name 3 is filtered and has only 1 value, give me Customer Name 3
AND (
ISFILTERED ( Customers[Customer Name 3] ),
HASONEVALUE ( Customers[Customer Name 3] )
), VALUES ( Customers[Customer Name 3] ),
//if multiple Customer Name 3 are selected, just remind me of that
ISFILTERED ( Customers[Customer Name 3] ), “Multiple Level 3 Selected”,
//replicate for Name 2
AND (
ISFILTERED ( Customers[Customer Name 2] ),
HASONEVALUE ( Customers[Customer Name 2] )
), VALUES ( Customers[Customer Name 2] ),
ISFILTERED ( Customers[Customer Name 2] ), “Multiple Level 2 Selected”,
AND (
ISFILTERED ( Customers[Customer Name 1] ),
HASONEVALUE ( Customers[Customer Name 1] )
), VALUES ( Customers[Customer Name 1] ),
ISFILTERED ( Customers[Customer Name 1] ), “Multiple Level 1 Selected”,
“No selection made”
)
And lastly, a version of the measure where all selected elements are concatenated with a comma (“,”):
SWITCH (
TRUE (),
//if Name 3 is filtered, pick up all values and concat them with a comma “,”
ISFILTERED ( Customers[Customer Name 3] ),
CONCATENATEX (
VALUES ( Customers[Customer Name 3] ),
Customers[Customer Name 3],
“,”
),
ISFILTERED ( Customers[Customer Name 2] ),
CONCATENATEX (
VALUES ( Customers[Customer Name 2] ),
Customers[Customer Name 2],
“,”
),
ISFILTERED ( Customers[Customer Name 1] ),
CONCATENATEX (
VALUES ( Customers[Customer Name 1] ),
Customers[Customer Name 1],
“,”
),
“No selection made”
)
Summary
Make sure to watch the video below 👇 to hear when and how should you use these DAX formulas.
And if you decide to replicate any of these methods, please let me know down in the comments below along with any other suggestion or solution that you may found in Power BI to solve the same problem.
Roland
Subscribe below 👇 to get full access to BI-Lingual Analytics Materials
💰 It’s free 💰
🙏Thanks for watching and if you liked this video please hit the 👍 button.
📢 Also be sure to subscribe to the channel so you never miss a new video.
0 Comments