Forum Discussion
Summarize table with dynamic filtering
OK. We can certainly compute distances without needing an extra calculated table in the model. For example,
Closest Profile = VAR E1 = AVERAGE ( Survey[Element1] ) VAR E2 = AVERAGE ( Survey[Element2] ) VAR E3 = AVERAGE ( Survey[Element3] ) RETURN MINX ( TOPN ( 1, Profile, ( E1 - Profile[_Element1] ) ^ 2 + ( E1 - Profile[_Element1] ) ^ 2 + ( E1 - Profile[_Element1] ) ^ 2, ASC ), Profile[Profile] )You can make all this dynamic without a calculated table:
See attached.
Kudos to AlexisOlson for asking the right question to move this forward!
Rather than TopN do what you want:Avg Distance Rank = RANKX( ALLSELECTED(Profile[Profile]), [Avg Distance], , ASC, Dense )and use it in the visual filter?
bcdobbs ok, I understand but I will try to break it down step by step as easily as I can because I do have a hard time explaining it so here goes.
Step 1: The first table is the Survey table which is the table that has all the data for the master survey taken by various respondents. The Survey has 4 questions that have various responses.
The 3 element fields are scores for each survey respondent.
Step 2: The second table is the profile table that has static data for various user profiles. This table is not related to that Survey table but does have the same 3 element fields. However, this table is static and does not change.
Step 3: This is where I need the magic to happen the profile calculated table is the union between the profile table and the survey table to create a new profile table that now includes the summarized average for the 3 elements from the surveys table. So the final output should be a new table or measure that combines the static profile table with the 3 elements and the aggregated brand as shown in the below screenshot.
Filtering: The second thing that I need and where I am stuck is the ability to filter the Survey questions and have that filter adjust the summarized Brand elements.
I hope this helps, its difficult to explain but that is the best I can do. please let me know if you need more context.
.
Ok, starting to understand! Not sure it's statistically valid (think you'd at least need the number of respondents for each profile type).That said I'll send you an example measure later this morning that I think does what you're asking.
Awesome! I await your reply.
Sorry PBI-Guy I'm going to have to check out of this one. Have had another look but I just don't understand what you're trying to achieve.
AlexisOlson don't suppose you could have a look, I'm probably missing something obvious!