Forum Discussion
Sjoerd_g
3 years agoFrequent Visitor
Using SELECTEDVALUE with SUMMARIZE
Hi all, I have a dilemma as I'm trying to create a visual that is seemingly very straightforward but I have tried several approaches, and nothing is what I'd like although I got quite close. ...
- 3 years ago
I managed to solve this and thought it might be useful for anyone else.
I set up Variables with the initially mentioned measure and used the SWITCH combined with SELECTEDVALUE to test if the name matched and returned the correct VAR.
RETURN SWITCH( TRUE(), SELECTEDVALUE(ratings[rating]) = "Atmosphere", _atmosphere, SELECTEDVALUE(ratings[rating]) = "Cleanliness", _Cleanliness, SELECTEDVALUE(ratings[rating]) = "Drink", _Drink, SELECTEDVALUE(ratings[rating]) = "Service", _Service, SELECTEDVALUE(ratings[rating]) = "Value", _Value )VAR example:
VAR _Value = AVERAGEX( FILTER( SUMMARIZE( ALLSELECTED(ratings), [response id], "Atmosphere", CALCULATE(AVERAGE(ratings[score]),SEARCH("Atmosphere", ratings[Translated Rating],,0)), "Cleanliness", CALCULATE(AVERAGE(ratings[score]),SEARCH("Cleanliness", ratings[Translated Rating],,0)), "Drink", CALCULATE(AVERAGE(ratings[score]),SEARCH("Drink", ratings[Translated Rating],,0)), "Food", CALCULATE(AVERAGE(ratings[score]),SEARCH("Food", ratings[Translated Rating],,0)), "Service", CALCULATE(AVERAGE(ratings[score]),SEARCH("Service", ratings[Translated Rating],,0)), "Value", CALCULATE(AVERAGE(ratings[score]),SEARCH("Value", ratings[Translated Rating],,0)) ), [Food] <= 2 ), [Value] )
Sjoerd_g
3 years agoFrequent Visitor
I managed to solve this and thought it might be useful for anyone else.
I set up Variables with the initially mentioned measure and used the SWITCH combined with SELECTEDVALUE to test if the name matched and returned the correct VAR.
RETURN
SWITCH(
TRUE(),
SELECTEDVALUE(ratings[rating]) = "Atmosphere", _atmosphere,
SELECTEDVALUE(ratings[rating]) = "Cleanliness", _Cleanliness,
SELECTEDVALUE(ratings[rating]) = "Drink", _Drink,
SELECTEDVALUE(ratings[rating]) = "Service", _Service,
SELECTEDVALUE(ratings[rating]) = "Value", _Value
)
VAR example:
VAR _Value =
AVERAGEX(
FILTER(
SUMMARIZE(
ALLSELECTED(ratings),
[response id],
"Atmosphere", CALCULATE(AVERAGE(ratings[score]),SEARCH("Atmosphere", ratings[Translated Rating],,0)),
"Cleanliness", CALCULATE(AVERAGE(ratings[score]),SEARCH("Cleanliness", ratings[Translated Rating],,0)),
"Drink", CALCULATE(AVERAGE(ratings[score]),SEARCH("Drink", ratings[Translated Rating],,0)),
"Food", CALCULATE(AVERAGE(ratings[score]),SEARCH("Food", ratings[Translated Rating],,0)),
"Service", CALCULATE(AVERAGE(ratings[score]),SEARCH("Service", ratings[Translated Rating],,0)),
"Value", CALCULATE(AVERAGE(ratings[score]),SEARCH("Value", ratings[Translated Rating],,0))
),
[Food] <= 2
),
[Value]
)