A matrix with expanding hierarchy and the ability to hide rows AND columns - is this possible?

[D_PBI]

Hi,

I have a requirement which I would help to achieve it please.

I have starting source data that is structured in this format:


The above starting source data is just a snippet of the full data. The full data has many more columns but for simplicity the above is fine.
I am aware that the subjects follow a hierarchy (which I'll have to apply in Power Query). Physics, Biology, and Chemistry are under the Science hierarchy level. Writing and Speaking are under the English hierarchy level. Algebra are under the Maths subject.

There are multiple slicers on the report which allow the user to filter on which subject are to be included in the Matrix. In this case the user has chosen Science and English.

The request is to have a Matrix visual where the user can expand/de-expand the parent hierarchy to show the subjects. For example, If the user expands Science then Physics, Biology, and Chemistry will show. If the user expands English then Writing and Speaking will shows. The below image shows the two expanded hierarchy levels.

So far evertthing seems straight forward, however there is a twist.
I would like the Matrix to show the following:
1) Only show People where they have at least have one '1' value in the slicer filtered rows. -- Notice how Steven doesn't have any '1' values above... Steven should not show in the Matrix.
2) Only show Subjects where at least person has a '1' for the subject. -- Notice how no people have a '1' value for the Biology... Biology should not show in the Matrix.

See below for the example for what the Matrix should look like.

Can this be done and how?

Thanks in advance.

[D_PBI]

Bump. Anyone?

[amitchandak]

@D_PBI 

Please find the solution at https://www.dropbox.com/s/3bs6hg1wc3taxi9/SubjectStidentWise.pbix?dl=0

Data I created: https://www.dropbox.com/s/2yscm4ijobke49n/studentdata.xlsx?dl=0

Steps:

1. Transformed the student, unpivoted the table. Refer: https://radacad.com/pivot-and-unpivot-with-power-bi
2. I have taken pre-existing subject and stream data. Joined it with the student using the subject
3. Created a matrix visual and, put a visual filter on opted <> 0
4. In Matrix visual, turn off the stepped layout and Drilled down

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[D_PBI]

@amitchandak - thank you for your response. Very helpful.

I can see how you have made it so that if the Subject has 0 (zero) across the line (meaning that no people have a 1 for that subject) then that particular Subject line is not visible. The 'Opted' column, being in the Values section for the Matrix visual, must calculate the total for that row (although the total isn't showing at the end of the row) as if that total is 0 then through the filtering applied the row is filtered out.

Keeping all the same, how do I also have it so that if a Person has all zeros for the Subjects selected in the slicers then that Person doesn't appear either?
Is this possible?  As the 'Name' column (the Person) is positioned in the Columns section for the Matrix visual I guess you cannot apply the same totalling (is not 0) logic as is present for the 'Opted' value - am I correct?

Can what I need be acheived via DAX? - any ideas how?

Thanks again.

[amitchandak]

I have not tested, but try creating a measure

sum(table[opted],allexcept(table[subject])) and try to filter this now. It is subject wise total.

[D_PBI]

Hi @amitchandak 

Thanks for your reponse again.

I didn't understand your DAX formula, however I have found that if I place the 'Opted' is not 0 filter as a Page filter (not a Visual filter) it removes any people that have 0 for all the subjects chosen, and also removes any subject rows where no person has a 1 for that subject row.

It would be nice to know why when placing the 'Opted' is not 0 in the Visual filter it only removes rows where no person has a 1 for that subject row, and why by placing the 'Opted' is not 0 filter in Page filter it removes both subject rows and people that have 0??

[amitchandak]

All expect is used for subtotal I was think to use same in filter

sum(table[opted],allexcept(table,table[subject])).

So this should only remove when the subject (across all persons) is 0. so should leave the person with 0 subject

[D_PBI]

@amitchandak  thanks again.

Can I ask for your help on another item I need completing.
Using the .pbix file you kindly provided, I am attempting to create a Tooltip that shows all the Subjects a Person is opted for. So if the use clicks on column header of Paul (so to set the SELECTEDVALUE to 'Paul') then a Tooltip appears listing (in a table visual or card visual, embedded in the Tooltip page) all the Subjects Paul is opted for, regardless of which Stream or Subject filtering has been applied.

In your example, Paul is opted in for Speaking, Algebra, Chemistry, and Physics. When the user click on Paul in the Matrix column header and then hovers over any cell in Paul's column I expect to see a Tooltip appear listing those Subjects. If the user filters the Stream or Subject (filters the Stream to 'Science') then I still expect the Tooltip to list all the Subjects that Paul is opted in for, ignoring all filtering (including the 'Science' Stream).

My Dax attempt on this is:
Person Opted=
CALCULATE (
    CONCATENATEX ( Subject, Subject[Subject], " " ),
    FILTER (
        Student,
        AND (
            Student[Name] = SELECTEDVALUE ( Student[Name] ),
            Student[Opted] = 1
        )
    )
)

Can you please help?  Thanks.

[amitchandak]

In your table or Matrix , if you are using student name, then only concatenate should work on tooltip

CALCULATE (
CONCATENATEX ( Subject, Subject[Subject], " " ))

[D_PBI]

@amitchandak - it's not working how I need. I even created the measure (as per your last post) and placed it inside a Table visual embedded in a Tooltip page (turning on that page's Tooltip toggle).

When I go back to the Matrix visual and hover over a cell the Tooltip page that flashes up only shows the Subject that is on the row for the cell being hovered over. Thinking that I need to click on the column header (say the column for Paul) to set a SELECTEDVALUE of Paul behind the scenes, I then hover over one of Paul's cells but the same result occurs in just a single Subject, the Subject for that row, is all that appears in the Tooltip. I require every subject for Paul to show in the Tooltip, ignoring any filters that may have been set in the filters/slicers - I need all the subjects to appear in the Tooltip for the column's person.

Are you able to try this requirement yourself using the .pbix you created?  If you can get it working then I can take a look at what you've done and follow it.

Thanks.

[D_PBI · Accepted solution]

@amitchandak  - I have it working a needed.
The measure I needed to produce, and embed in the Tooltip, is below:

__Tooltip =

CALCULATE(

          CONCATENATEX(

                                      Subject,

                                      Subject[Subject],

                                      " "

                                     ),

          ALL( Subject )

)

All works now. Thanks for your help.