Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tomtang
Helper III
Helper III

Capture selected value from multiple slicers on same column

Hi all,

 

I am trying to create a report which has 2 matrices, let's say 1 for "Sales" and the other for "Gross Margin"

 

These 2 charts need to be dynamically toggled between "Sales" to "Service" and "Gross Margin" to "Service GP", so I set 2 slicers on both matrices on the same column (for selecting accounts) in order to toggle between different accounts (e.g. "Sales" to "Service")

 

All the above mentioned are working properly

The question is, I would like to understand how to create 2 dynamic titles for these 2 matrices? (To enhance the usability, so user know which matrix is for which account)

Because I know even if I use "SELECTEDVALUES", the DAX can't capture any value from the slicers.

Any possibilities to make this idea happen?

 

Thanks guys!

1 ACCEPTED SOLUTION

Oh so you have changed the filter interactivity to block the slicer from affecting the other matrix. In this case you can do this by setting the conditional formatting on the matrix to a measure that uses SELECTEDVALUE. 

 

201912 conditional title.png

First create a measure for your title:

Dynamic Title = "Matrix for " & SELECTEDVALUE('Fact'[Account],"Unknown") 

 

Then Click on the matrix, go into the title property and hover over the text fields until the "three dots" option appears and then click that to get into the conditional formatting dialog

201912 conditional title 1.png

Then choose the measure you created as the field for the Title text.

201912 conditional title 2.png

View solution in original post

11 REPLIES 11
loke
Regular Visitor

Hey folks!..
My scenario : I have a report page which has 2 slicers which is fetching value from the same table and same column, i have used edit interactions to show 2 chars based on the above to slicer selections.

what i need : I need to get the value selected in each slicers in two different variables inside a measure. I tried using SELECTED VALUE but returns blank always.

  could some one give me any ideas...!


@loke wrote:

My scenario : I have a report page which has 2 slicers which is fetching value from the same table and same column, i have used edit interactions to show 2 chars based on the above to slicer selections.


Using 2 slicers on the same column will not work. If your slicer was months for example and you selected June and July this sort of configuration would generate a filter which logically says " show me all rows where the month equal June and July" and there will be no rows where this is true resulting in your blank value.

 

Instead you need to either use 2 different tables in your slicer (one or both could be disconnected and you could then do your filtering in a measure)

OR you would need to use a single slicer and get people to multi-select the 2 items. 

Anonymous
Not applicable

Hello there

 

how to capture multi-values from a slicer but; from a multidimensional olap cube?

@Anonymous You can use VALUES to return a table with a single column containing multiple rows (which is equal to multiple values)

 

Maybe you could share more what are you trying to achieve, and some more context for us to simulate!

 

Anonymous
Not applicable

I'm sending the PBI reports by email in a PDF format. And i'm printing the values of the parameters that we selected in text lines. So when you select multiple values from a filter the value that it is showing is "All" instead the multiple values i selected. The chalenge here is the parameters come from a Multidimensional OLAP cube. So for example the Company filter it is a Dimension from our multidimensional cube.

 

thanks!

d_gosbell
Super User
Super User


@tomtang wrote:

Because I know even if I use "SELECTEDVALUES", the DAX can't capture any value from the slicers.

 


I'm not sure why you would say this. If the slicers are single select then SELECTEDVALUES should work just fine.

 

If they can be multi-select you would have to use an expression like CONCATENATEX( VALUES( <column> ) , <column>, ",") to get the list of selected values.

@d_gosbell 

 

Thanks for replying.

The reason is being because I have 2 similar matrices (e.g. Sales & Gross Margin) which link to 2 slicers (e.g. A & B slicers, these 2 slicers are referring to the same column) >>> Only single select for each slicer. 

 

I want to capture A slicer's value and show the selected value on to 1st matrix (Sales) title, and B slicer for 2nd matrix separately and respectively.

 

So CONCATENATEX might not work for this case. Or not sure if CONCATENATEX can identify which selected value is from 1st slicer, and which is from 2nd slicer (even when 2 slicers within the same page are referring to the same column)?

 

Thanks a lot!


@tomtang wrote:

The reason is being because I have 2 similar matrices (e.g. Sales & Gross Margin) which link to 2 slicers (e.g. A & B slicers, these 2 slicers are referring to the same column) >>> Only single select for each slicer. 

 


I can't think how you have this working. Are you able to create a simple example pbix report with dummy data and upload it?

 

If you have 2 slicers on the same underlying column they will cross filter each other by default so it would be impossible to select 2 different values. And even if you turn off the cross filtering, when you select 2 different values you will get no data as the 2 slicers will cancel each other out because the filter conditions get combined with a logical AND. 

@d_gosbell 

 

Thanks for following up the question.

 

Kindly find the link for the sample file.

 

https://1drv.ms/u/s!AuUOyRlH3Y79g5FwcCn5WWhWpJcUJg

 

Thank you!

Oh so you have changed the filter interactivity to block the slicer from affecting the other matrix. In this case you can do this by setting the conditional formatting on the matrix to a measure that uses SELECTEDVALUE. 

 

201912 conditional title.png

First create a measure for your title:

Dynamic Title = "Matrix for " & SELECTEDVALUE('Fact'[Account],"Unknown") 

 

Then Click on the matrix, go into the title property and hover over the text fields until the "three dots" option appears and then click that to get into the conditional formatting dialog

201912 conditional title 1.png

Then choose the measure you created as the field for the Title text.

201912 conditional title 2.png

@d_gosbell 

 

Oh my! I didn't think the "SELECTEDVALUE" know which slicers to refer to!

 

Thank you so much! 😀

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.