Create new data table on the fly based on slicer selections

[veronicaxge]

I have one massive table with several categorical columns (age group, education, location, affiliation, etc.) that serve as filters and two numerical columns with the values of interest such as test scores. 

Individual ID	Age Group	Education	Location	Affliation	Test Subject	Test Score 2018	Test Score 2019
1	age group 1	bachelor's	Belgium	XXX	Subject A
1	age group 1	bachelor's	Belgium	XXX	Subject B
1	age group 1	bachelor's	Belgium	XXX	Subject C
2	age group 3	High school	Singapore	YYY	Subject A
2	age group 3	High school	Singapore	YYY	Subject B
2	age group 3	High school	Singapore	YYY	Subject C
3	age group 8	master's	South Africa	ZZZ	Subject A
3	age group 8	master's	South Africa	ZZZ	Subject B
3	age group 8	master's	South Africa	ZZZ	Subject C
... (more records)

The goal is to allow users to do one round of filtering with slicers on the categorical columns to query out all scores for one individual, and then a second round of filtering to query out those for a second individual.  And then, feed the queried information into a new table that looks like below. This is intended to be a table in the data model, not a visualization table on the canvas. 

Individual ID - First Individual	Individual ID - Second Individual	Test Subject	Test Score 2018 - First Individual	Test Score 2019 - First Individual	Test Score 2018 - Second Individual	Test Score 2019 - Second Individual
1	3	Subject A
1	3	Subject B
1	3	Subject C

The requirement is that however the end users filter the data, this resulting table will draw the filtered results accordingly.

After long searching and video watching I still can't find how to effectively achieve this in Power BI. Any advice is appreciated! 

[Anonymous]

Hi @veronicaxge,

Please try to use matrix as below and check if it can satisfy your requirement:

Best Regards

Rena

[Greg_Deckler]

Just so we are clear, tables are calculated at the time of data load and are not dynamic. Thus, if you are expecting user interactivity then that user interactivity would have to come from setting measure values in a query if you want to end up with a "dynamic" table in the model. Is that OK?

[amitchandak]

You need first and second individual both row and column ?

[veronicaxge]

@amitchandak @Greg_Deckler @Anonymous  Thanks for your responses and clarifying questions!!

In the actual model I'd have thousands of individuals, each with scores from the three test subjetcs. I'd hope that users could use slicers to define two "subgroups" of individuals of certain age groups, education levels, and locations. For example subgroup 1 is all individuals who are in age group 1 with bachelor's degree in Belgium (which might not be only the Individual 1 in the example table; it could be any individual who has these characteristics); subgroup 2 is individuals in age group 8 with master's in South Africa. 

Would I be able to then feed these two groups into a new table with the structure I specified above? The plan is to then create four bars or four lines based on this new table to compare test scores 2018 and 2019 for the first group as well as scores 2018 and 2019 for the second group. 

[Anonymous]

Hi @veronicaxge ,

Could you please provide your final expected result? If the following is your expected result?

Best Regards

Rena

[veronicaxge]

@Anonymous Hi Rena,

Thanks for your screenshot! How did you achieve the table in your screen shot? 

Two points for further clarification: 

- The table on your screenshot is definitely my expected result but I would hope the table to become a part of the data model, instead of a visual table on the canvas. Is that possble? 

- Another thing is that since the data model has thousands of people with different combinations of age, education, location,  I must be able to allow user to do the slicing on all three parameters for the two "subgroups", and accurately draw those two "subgroups" into the table structure in your screen shot. Is that achieveable with this table setting? 

[Anonymous]

Hi @veronicaxge,

The user can select the corresponding subgroup(combinations of age group, education and location) through the options of the three slicers, then view the individuals information about the corresponding subgroup. Here I share my example PBIX file with you, you can download it from this link. Please check whether that is what you want...

Best Regards

Rena

[veronicaxge]

Hi Rena,

Thanks for your PBIX file! Very neat setup. 

What I'm trying to get at is whether it is possible to make that matrix table you created in the canvas as a part of the data model? I'm hoping to use that filtered table to create more visualization such as four bars or four lines on the same chart (e.g. ID1-2018 score, ID1-2019 score, ID3-2018 score. ID3-2019 score). In this current setup I am not able to create such charts.  

Thanks for your advice! 

[Anonymous]

Hi @veronicaxge ,

Please try to use report page-level filter or sync slicer, you can refer the following documentations.

https://docs.microsoft.com/en-us/power-bi/power-bi-report-add-filter#add-a-filter-to-an-entire-page

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers#sync-and-use-slicer...

Best Regards

Rena