cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
giogiogio Helper II
Helper II

Multiple value slicer

Hello everybody: maybe a stupid question from my side, but I am struggling to understand how to solve the following problem which I try to simplify as much as possible in the following example.

I have a table where ID is a value that identifies a unique "project" (and is used to link different tables as this value is unique) and Category is the category within this project falls into.

IDCategory
1A
2B
3A & B
4A


I'd like now to create a slicer that refers to a single category only:
- if I select B I want to see filtered all projects within category B, thus 2 and 3
- if I select A I want to see filtered all projects within category A, thus 1, 3 and 4.

I was thinking about splitting the entries (e.g. two ID 3 one with A as Category and one with B) but then the one-to-one relationship between tables would be lost.

Is there any workaround to solve this?

Many thanks in advace!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Multiple value slicer

Hi @giogiogio ,

 

Do like this:

1. Creating a "Category" table by using "Enter data" feature:

k1.PNG

2. Creating a measure named "Category_" and adding the measure to the table visual:

Category_ = 
VAR x = 
IF(
    CONTAINSSTRING(
        SELECTEDVALUE(Sheet1[Category]),
        SELECTEDVALUE(Category[Category])
    ),
    TRUE(), FALSE()
)
RETURN
IF( 
    x = TRUE(),
    MAX(Sheet1[Category])
)

 k2.PNG

 

Since there is no created relationship between the two tables, if you do not add the created measure to the chart, you cannot complete the filtering.

Hope to meet your requirements.

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Super User IV
Super User IV

Re: Multiple value slicer

@giogiogio 

Create a new category table with only value A and B

Do not join. try something like this

 


measure
var _max = maxx(Category,Category[Category])
return
if(isfiltered(Category[Category]),calculate(count(Table[ID])),calculate(count(Table[ID]),search(_max,Table[Category],,0)>0))

Add this measure to visual





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin


Super User IV
Super User IV

Re: Multiple value slicer

One to many relationships are not the end of the world.

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Community Support
Community Support

Re: Multiple value slicer

Hi @giogiogio ,

 

Do like this:

1. Creating a "Category" table by using "Enter data" feature:

k1.PNG

2. Creating a measure named "Category_" and adding the measure to the table visual:

Category_ = 
VAR x = 
IF(
    CONTAINSSTRING(
        SELECTEDVALUE(Sheet1[Category]),
        SELECTEDVALUE(Category[Category])
    ),
    TRUE(), FALSE()
)
RETURN
IF( 
    x = TRUE(),
    MAX(Sheet1[Category])
)

 k2.PNG

 

Since there is no created relationship between the two tables, if you do not add the created measure to the chart, you cannot complete the filtering.

Hope to meet your requirements.

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

giogiogio Helper II
Helper II

Re: Multiple value slicer

Thank you lionel, yours is the best for my needs.
Thank you also to others that replied to my question.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors