Forum Discussion
Dynamic rows, columns and values in matrix
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
- v-chuncz-msft7 years agoCommunity Support
You may adjust <Expression> in SWITCH.
https://community.powerbi.com/t5/Desktop/numeric-dstribution-detention/td-p/519999
- jscottNRG7 years agoHelper II
Hi v-chuncz-msft, thanks for your help.
I'm afraid I'm still not clear what changes should be made to to the <Expression> part of SWITCH to achieve what I need. Could you please suggest edit(s) to my code? I think you're suggesting that I add to the
SELECTEDVALUE('attribute_slicer (1)'[Attribute],"")term of the HERS Average measure, but I'm unsure how to tie in 'attribute_slicer (2)'[Attribute].
Thanks again.
J
- jscottNRG7 years agoHelper II
I'm still hoping that someone experienced might be able to help with this, any suggestions?