Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
ID | Category |
1 | A |
2 | B |
3 | A & B |
4 | A |
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!
Solved! Go to Solution.
Hi @giogiogio ,
Do like this:
1. Creating a "Category" table by using "Enter data" feature:
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])
)
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.
Thank you lionel, yours is the best for my needs.
Thank you also to others that replied to my question.
Hi @giogiogio ,
Do like this:
1. Creating a "Category" table by using "Enter data" feature:
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])
)
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |