Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
I have a table with task names, categories , bunch other details associated with tasks. The table is composed as below
taskName | Role | details |
Task 1 | Dev | abc |
Task 2 | QA | abc |
Task 3 | Dev | abc |
Task 4 | QA | abc |
Task 5 | Manager | abc |
Task 6 | Supervisor | abc |
Task 7 | All | abc |
Task 8 | All Other | abc |
Task 9 | All | abc |
Now, when create a slicer based on Role column ( created dimension out the it and used it for slicer) and applied QA Role the matrix chart slices the data for only task 2 and task 4. But the requirement is show task 2, task 4 and task 7, task 8, task 9 along with. So apart from the selection of All and All other roles which ever is selected it should show that role task and the default Role tasks.
For this I created Flag metric and applied on the visual
Flag metric:
Flag =
var _selectedRole = selectedvalue(RoleDimension[Role])
return
IF
(
OR(
MAX(Table[Role]) = _selectedRole, MAX(Table[Role]) = "All"), 1, 0
)
Expectation from the above was it would assign the value of 1 to rows where Role is "All" and selected Role from slicer. But the visual only filters for selected Role leaving out the defaults rows.
Expected results
When Role = QA, it should show as below
taskName | Role | details |
Task 2 | QA | abc |
Task 4 | QA | abc |
Task 7 | All | abc |
Task 8 | All | abc |
Task 9 | All | abc |
How to acheive this functionality. Any help and approach to this problem would helpful.
Solved! Go to Solution.
Hello @Dataseeker009
To simplify the process, you can take the following steps:
Create a dimension table as shown:
Next, establish a relationship with these properties:
This should yield the desired outcome, as illustrated here:
This method is a quick fix, but it may lead to complications with more complex data models in the future. A more effective solution would require a deeper understanding of your data model's structure and intricacies.
For straightforward data models, this approach should suffice and can be implemented.
Best Regards,
Udit
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍
🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree
Thank you for reply and solutions quantumudit , @Kedar_Pande , @Anonymous.
The solution using the flag metric still filters the main table of exclusive filters on the matrix table, But the solution of creating a composite Role dimension works in current context of my problem.
Hi @Dataseeker009 ,
Thanks for the reply from Kedar_Pande and quantumudit , please allow me to provide another insight:
The OR function in DAX takes only two arguments, so you can use the OR operator (||) to join all calculations into a simpler expression.
Flag =
VAR _selectedRole = SELECTEDVALUE(RoleDimension[Role])
RETURN
IF (
MAX('Table'[Role]) = _selectedRole ||
MAX('Table'[Role]) = "All" ||
MAX('Table'[Role]) = "All Other",
1,
0
)
It is worth noting that do not create a relationship between the two tables.
The final result is shown below, more details can be found in the attachment.
If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Flag measure:
Flag =
VAR _selectedRole = SELECTEDVALUE(RoleDimension[Role])
RETURN
IF (
OR (
MAX(Table[Role]) = _selectedRole,
MAX(Table[Role]) = "All",
MAX(Table[Role]) = "All Other"
),
1,
0
)
Add a visual level filter using the Flag measure:
Set the filter condition to show only rows where Flag equals 1.
Make sure your slicer is based on the RoleDimension[Role] field, so when users select "QA", the Flag measure will determine which tasks to display.
Hello @Dataseeker009
To simplify the process, you can take the following steps:
Create a dimension table as shown:
Next, establish a relationship with these properties:
This should yield the desired outcome, as illustrated here:
This method is a quick fix, but it may lead to complications with more complex data models in the future. A more effective solution would require a deeper understanding of your data model's structure and intricacies.
For straightforward data models, this approach should suffice and can be implemented.
Best Regards,
Udit
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍
🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree
Flag =
VAR _selectedRole = SELECTEDVALUE(RoleDimension[Role])
RETURN
IF (
OR (
MAX(Table[Role]) = _selectedRole,
MAX(Table[Role]) = "All",
MAX(Table[Role]) = "All Other"
),
1,
0
)
User | Count |
---|---|
14 | |
9 | |
7 | |
7 | |
6 |
User | Count |
---|---|
21 | |
11 | |
10 | |
10 | |
8 |