Choose hierarchy order for matrix using slicer

Hi, I see a lot peripherally related to this, but haven’t found an answer yet. Can anyone provide one or steer me towards a resource?

I have 2 dim tables, location and person, plus fact table. I have a matrix and would like to use a slicer to dynamically switch between viewing by location then person, or by person then location. I imagine this involves switching the contents of the first two columns of the matrix and then using the stepped layout.

I can’t use bookmarks to switch between two matrix visuals because cross-highlighting is lost as soon as a visual is made invisible.

I appreciate any help. Thanks!
John

@jpr,

I’m wondering if perhaps the new page navigation features in the March update can be used to solve this problem? Instead of using slicers to switch between the tables, could you place the location by person matrix on one page, and the person by location matrix on another and then use buttons linked to page navigation actions to switch between the two and back? I’ll admit I haven’t played with this feature much yet since its release, but it seems like it may fit this use case well.

I hope this is helpful. Please let me know what you think.

  • Brian
1 Like

Thanks, Brian. That’s worth a try. I’ll post back.

I was able to set things up with a slicer to change visuals, but it didn’t really solve my real problem. I set up a table consisting of combinations twice; once for a dim column set to person and one for it set to location. ColA and ColB were assigned to Person/Location or Location/Person appropriately.

This made the matrix display exactly as I expected, but cross-filtering doesn’t work because ColA and ColB are set to different things in the matrix (e.g. ColA=Jack, ColB=School in one matrix and the reverse in the other). Any ideas?

Thanks.

I admit to not knowing a lot about how cross-highlighting works under the covers, but what I would ideally want is the selection and highlighting occurring based on a (hidden)composite key if that gives anyone any ideas.

@jpr,

This is getting a little difficult for me to visualize. Can you please post a PBIX file and a mockup of the result you’re looking for?

Thanks.

  • Brian

Sure. I greatly appreciate the help.

This .pbix has a slicer that lets you choose Location or Person as the first column in the matrix.
The .mp4 shows the slicer switching the fields in the first two columns.
Only issue is that selecting (for example) Anna/School does not result in School/Anna being highlighted when you change the slicer (since it’s trying to match ColA values to values that are now in ColB). (FYI, pardon that two-column join workaround)
Thanks.
-jpr

DynamicGrouping.pbix (126.9 KB)
2020-03-16_12-44-02|video

1 Like

Thanks for posting your question @jpr. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, a screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

@jpr,

Nice. This is a really clever construct and data model – I’ve not seen this particular setup before.

If I understand correctly what you trying to do with regard to the highlighting “sticking” when you switch dimensions, I created the following measure, which I then used in the column field value conditional formatting:

Cond Fmt Person = 

IF(
    [Harvest Dim] = "Location" && SELECTEDVALUE( DistinctCombined[ColB] ) = [Harvest Person],
       "orange",
        IF( [Harvest Dim] = "Person" && SELECTEDVALUE( DistinctCombined[ColA] ) = [Harvest Person],
            "orange",
            BLANK()
        )
) 

For the [Harvest Person] measure, I first had to create a disconnected supporting table based on VALUES( Person[Person] ) in order to keep the selection from filtering the table.

This all works nicely for tables, but is pretty wonky for matrixes, since conditional formatting of matrix row headers is not supported yet in Power BI:

image


image

Hope this is at least marginally helpful. If I’ve missed the mark on what you were looking for, let me know and I’ll take another swing at it.

4 Likes

Great solution Brian.

Hi @jpr , we’ve noticed that no response has been received from you since the 17th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

Very helpful, Brian. Appreciate the assistance.