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?
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.
AlexisOlson my goodness thanks so much! this gets me much closer now. the only issue I have is I need to plot it on a scatter chart like below.
And I also need to make the top N variable between 5-10
Thanks again Alexis
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?
I think AlexisOlson deserves the solution on this one. I just wrapped his work in a rankx.
Top N what though? What measure does it affect (or do you just want to limit the rows showing up on the right)?
AlexisOlson It would affect the "closest profile" measure to limit how many rows are showing (Dynamic between 5-10).