Forum Discussion
How to return all the text values without using aggregates?
This is my measure
I am trying to return "place - xx%"
Location_Sli =
VAR _overallloc =
CALCULATE (
ROUND ( [Overall % Mgmt] * 100, 0 ),
ALLEXCEPT ( 'Staffing Report', 'Staffing Report'[Location] )
)
VAR _redgreen =
IF ( _overallloc >= 90, "🟢", "🔴" )
VAR _location_sli =
IF (
MIN ( 'Staffing Report'[Location] ) = BLANK (),
BLANK (),
CONCATENATE (
CONCATENATE (
CONCATENATE ( _redgreen, MIN ( 'Staffing Report'[Location] ) ),
"-"
),
_overallloc & "%"
)
)
RETURN
_location_sli
I want to return all the location names and this is a measure, the last part of the measure which is var _location_sli is asking for aggregates and when I give min, I get only one location but I need all the location names. I cant create a calculated column, as that wont change my metric.
Please help.
Anonymous , When you create a measure, It will take measure or column with aggregation (which is also a measure)
Measure return only one value. In this case, I have to plot this measure in visual with 'Staffing Report'[Location].
If we need location only to process some calculation and can work without it in visual (Kind of LOD) , Then we can have
sumx(Values('Staffing Report'[Location]), [Measure])
means measure will calculated till 'Staffing Report'[Location] after that it will sumup
2 Replies
Anonymous , When you create a measure, It will take measure or column with aggregation (which is also a measure)
Measure return only one value. In this case, I have to plot this measure in visual with 'Staffing Report'[Location].
If we need location only to process some calculation and can work without it in visual (Kind of LOD) , Then we can have
sumx(Values('Staffing Report'[Location]), [Measure])
means measure will calculated till 'Staffing Report'[Location] after that it will sumup
Thanks amitchandak I got your explanation.