Forum Discussion

jscottNRG's avatar
jscottNRG
Helper II
7 years ago

Dynamic rows, columns and values in matrix

I'd like to create a matrix visual that allows users to compare data by different intersections of attributes.  Specifically, I'd like the users to be able to see the average values of different new ...
Need Help
jscottNRG's avatar
jscottNRG
Helper II
7 years ago

Here's an update on my original post.

 

I found a fantastic tutorial that outlined how to do something similar, here.  The author describes how to make dynamic dimensions in order to use a slicer to update a table visual.  I won't rehash the entire tutorial here, but it worked as expected and I was able to make the matrix rows responsive to my slicer selections.  Updated screenshots of my report and relationships:

 

 

 

And the DAX:

 

dim_consolidated (1) = UNION(UNION(UNION(UNION(UNION(
FILTER('dim_hometype (1)', 'dim_hometype (1)'[dim]<>BLANK()), 
FILTER('dim_supervisors (1)', 'dim_supervisors (1)'[dim]<>BLANK())), 
FILTER('dim_subdivisions (1)', 'dim_subdivisions (1)'[dim]<>BLANK())),
FILTER('dim_models (1)', 'dim_models (1)'[dim]<>BLANK())),
FILTER('dim_contractors_HVAC (1)', 'dim_contractors_HVAC (1)'[dim]<>BLANK())),
FILTER('dim_contractors_insul (1)', 'dim_contractors_insul (1)'[dim]<>BLANK()))

This is the measure that I'm displaying for the "Values" of the matrix visual.

 

HERS Average = if(ISFILTERED('attribute_slicer (1)'[Attribute]) && HASONEVALUE('attribute_slicer (1)'[Attribute]),
				SWITCH(SELECTEDVALUE('attribute_slicer (1)'[Attribute],""),
						"Home Type",CALCULATE(AVERAGE(Job_info[HeRS]),USERELATIONSHIP(Job_info[Home Type],'dim_hometype (1)'[dim])),
						"Supervisor",CALCULATE(AVERAGE(Job_info[HeRS]),USERELATIONSHIP(Job_info[Supervisor],'dim_supervisors (1)'[dim])),
                                                "Subdivision",CALCULATE(AVERAGE(Job_info[HeRS]),USERELATIONSHIP(Job_info[Subdivision],'dim_subdivisions (1)'[dim])),
                                                "Model",CALCULATE(AVERAGE(Job_info[HeRS]),USERELATIONSHIP(Job_info[Model],'dim_models (1)'[dim])),
                                                "HVAC Contractor",CALCULATE(AVERAGE(Job_info[HeRS]),USERELATIONSHIP(ContractorInfo[ContractorName],'dim_contractors_HVAC (1)'[dim])),
                                                "Insulation Contractor",CALCULATE(AVERAGE(Job_info[HeRS]),USERELATIONSHIP(ContractorInfo[ContractorName],'dim_contractors_insul (1)'[dim])),
						"",AVERAGE(Job_info[HeRS])),
			         AVERAGE(Job_info[HeRS]))

If you read through this last code, you'll see that the measure is responsive to a selection in the "Row Attribute" slicer, which uses the "Attribute" column of the attribute_slicer (1) table.

 

However, I'm unsure how to adjust the "HERS Average" measure so that it is also responsive to a selection made in the "Column Attribute" slicer (uses the "Attribute" column of the attribute_slicer (2) table, which is set up in the same way as attribute_slicer (1)).  I think I need more USERELATIONSHIP terms added to the "HERS Average" measure to tie in the second (column) slicer, but I'm still fairly new to DAX and Power BI so I'm stuck again.

 

My data model is pretty complex and I imagine that more details will be needed, please let me know what I can clarify.  Thanks in advance for any assistance!

J