Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
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:
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
See this! This soulution very easy..
https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-personalize-visuals
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
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
You may adjust <Expression> in SWITCH.
https://community.powerbi.com/t5/Desktop/numeric-dstribution-detention/td-p/519999
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |