topic Re: How to show default Rows in Matrix Visual Chart even though Slicer is applied on page. in DAX Commands and Tips

How to show default Rows in Matrix Visual Chart even though Slicer is applied on page.

Dataseeker009 — Tue, 08 Oct 2024 07:39:19 GMT

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.

Re: How to show default Rows in Matrix Visual Chart even though Slicer is applied on page.

Kedar_Pande — Tue, 08 Oct 2024 08:00:02 GMT

Flag =
VAR _selectedRole = SELECTEDVALUE(RoleDimension[Role])
RETURN
IF (
OR (
MAX(Table[Role]) = _selectedRole, 
MAX(Table[Role]) = "All", 
MAX(Table[Role]) = "All Other"
), 
1, 
0
)

Re: How to show default Rows in Matrix Visual Chart even though Slicer is applied on page.

quantumudit — Tue, 08 Oct 2024 08:13:27 GMT

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

Re: How to show default Rows in Matrix Visual Chart even though Slicer is applied on page.

Kedar_Pande — Tue, 08 Oct 2024 10:41:10 GMT

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.

Re: How to show default Rows in Matrix Visual Chart even though Slicer is applied on page.

Anonymous — Wed, 09 Oct 2024 02:37:26 GMT

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.

Re: How to show default Rows in Matrix Visual Chart even though Slicer is applied on page.

Dataseeker009 — Wed, 09 Oct 2024 04:31:25 GMT

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.