Comparing the same field in a Matrix visual

[elJukes]

Hi there

I have a table of data:

Individual ID	Group	Year	Test Area	Test Type	Outcome
1	Alpha	16	Language	Test A	AT
1	Alpha	16	Language	Test B	ABV
1	Alpha	16	Language	Test C	ABV
1	Charlie	15	Language	Test A	AT
1	Charlie	15	Language	Test B	ABV
1	Charlie	15	Language	Test C	BLW
2	Alpha	16	Language	Test A	BLW
2	Alpha	16	Language	Test B	BLW
2	Alpha	16	Language	Test C	AT
3	Alpha	16	Language	Test A	AT
3	Alpha	16	Language	Test B	AT
3	Alpha	16	Language	Test C	AT
4	Bravo	16	Language	Test A	ABV
4	Bravo	16	Language	Test B	AT
4	Bravo	16	Language	Test C	ABV
5	Bravo	17	Language	Test A	AT
5	Bravo	17	Language	Test B	ABV
5	Bravo	17	Language	Test C	BLW

What I'd like to be able to do is this:

For everyone in group Alpha, in year 16, where the test area is Language count the number of people who were ABV/AT/BLW and are now ABV/AT/BLW like this:

 

I'd like to be able to use slicers to change the Group, Year and Test Area the information in the chart relates to. Essentially I want to see if individuals achieve different outcomes from Test A -> Test B -> Test C or maybe from Test A -> Test C.

An inidvidual can only be in one group, but can have outcomes from several years. Individuals will also have outcomes for different test areas and different test types. The outcomes themselves are limited to just ABV, AT or BLW.

I've considered several ways of doing it but have problems with each:

Firstly add a series of columns to the data table or pivot the data table so the Test Types become columns containing their respective Outcomes. This would allow me to compare results within the same year but not across years using filtering. I also need to keep the datatable in its original format for a different type of analysis within the same report.

Secondly create a series of subtables in DAX, with one table for each Test Type. This works in conjunction with an 'Individuals' table that contains their ID and the Group they are attached to, but I then lack the ability to filter down to a specific group & year combination.

Please help!

[v-chuncz-msft]

@elJukes,

You may drag multiple fields(Year, Test Type, Outcome) to Columns of the Matrix visual and drill down on columns.

[elJukes]

Hi @v-chuncz-msft

Thanks for your advice. I've had a go at using drilldown and didn't manage to get the matrix looking like I need.

As a work around I duplicated the data table so I could map Test A results from table 1 against Test B results from table 2. It works but it means DAX having to duplicate the entire dataset at every refresh, which doesn't sound efficient to me.

Could you advise how to use drilldown to get the figures I'm looking for, or if there's a better way of getting the result?

Thanks

Sam