Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jscottNRG
Helper II
Helper II

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 home performance statistics (HERS, ACH50, Final Fail Rate, etc.), for example by Home Type and Job Status (shown below), or by HVAC Contractor and Subdivision, etc.  The Values, Rows and Columns should be dynamic based on slicer selections.

 

Here's a screenshot of where I'm currently at, showing a non-dynamic matrix visual comparing average HERS scores for the cross-sections of Home Type and Job Status.  Note that I've added the slicers for Row Attribute, Column Attribute, and Value, but these are just showing how I'd like the final product to work and are not yet functional.

 

matrix and slicers.png

 

 

The options that I want available in the rows and columns of the matrix are in dimension tables, and the values are in a fact table, like this:

 

relationships.png

 

I've found good tutorials on how to make bar chart data dynamic based on a slicer selection (like this), but my desired setup is a little more complicated and I'm not sure how to proceed.  I also know that this could be achievable with bookmarks, but because I have 7 row/column options and 6 value options, I think the number of possible combinations is way too high to build a bookmark for each.  Has anyone done this before, or have suggestions for how to achieve this?

Thanks,

J

10 REPLIES 10
Anonymous
Not applicable

See this! This soulution very easy..

Let users personalize visuals in a report

https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-personalize-visuals

Anonymous
Not applicable

I hope you get an answer, i have the same problem... 

@Anonymousno solution yet.  I've left the report page as I described in my post above, with the row attribute determined dynamically with a slicer but the column attribute and displayed value fixed.  It's not what I originally wanted to achieve but I think it's still useful.

Hi I have something similar that i need to create but probably a step further. I pull the data from a view which has consolidated data for 3 years based. This view has various dimensions and summarized metric values like net sales, net sales LY, qunatity, quantity ly. Now the report that i am asked to create is dynamic selection criteria for rows and columns which act as group by for the facts that need to shown. It will be really helpful if you can provide me with the PBIX that you created just so i can recreate the same stuff and work on the fact bit. Thanks in advance

jscottNRG
Helper II
Helper II

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:

 

matrix and slicers 2.png

 

relationships 2.png

 

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

@jscottNRG,

 

You may adjust <Expression> in SWITCH.

https://community.powerbi.com/t5/Desktop/numeric-dstribution-detention/td-p/519999

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

I'm still hoping that someone experienced might be able to help with this, any suggestions?

Hi all,

I'm commenting on my question again to bump it to the top of the list.  If anyone has guidance on this - even to say that it's not possible - I'd very much appreciate it.  I'm unable to continue on my report until I get this issue solved and would like to be able to communicate to the executive team whether or not this visual is possible as I've described.

Thanks in advance,

J

One more try!  Please advise if anyone has guidance on what I'm trying to accomplish here.  If it's not possible I will scrap this report and explore other options.

Thanks,

J

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.