Help with DAX and measures

[Bennyboysmith]

Hi there,

I am a power BI novice and have been struggling this morning to create a single column in a dashboard table that assesses a calculated score for each value in a single dimension.

The table I am working on looks like this:

Factor	Measure	GWB Measure	HWI Measure	JCS Measure	CAW Measure	WCS Measure	SAW Measure
CAW	8	Lower QoWL	Higher QoWL	Lower QoWL	Lower QoWL	Average QoWL	Average QoWL
GWB	16.8	Lower QoWL	Higher QoWL	Lower QoWL	Lower QoWL	Average QoWL	Average QoWL
HWI	12	Lower QoWL	Higher QoWL	Lower QoWL	Lower QoWL	Average QoWL	Average QoWL
JCS	16	Lower QoWL	Higher QoWL	Lower QoWL	Lower QoWL	Average QoWL	Average QoWL
SAW	4.4	Lower QoWL	Higher QoWL	Lower QoWL	Lower QoWL	Average QoWL	Average QoWL
WCS	9.6	Lower QoWL	Higher QoWL	Lower QoWL	Lower QoWL	Average QoWL	Average QoWL

What I am trying to do is to have a single column that assesses the measure for each category depending on whether the measure falls between ranges (the ranges are different for each value). I have managed to do this by creating measures for each category separately but I cant seem to create 1 column that shows the 'assessment' for each value

So what i want is this:

Factor	Measure	Assessment
CAW	8	Lower QoWL
GWB	16.8	Lower QoWL
HWI	12	Higher QoWL
JCS	16	Lower QoWL
SAW	4.4	Average QoWL
WCS	9.6	Average QoWL

The Measure is:

AVERAGE('Survey Data'[Score])*DISTINCTCOUNT('Category Groups'[QuestionID])

I am using this expression to calculate the assessments for each individual category, for for the GWB assessment:

If(Calculate(AVERAGE('Survey Data'[Score])*DISTINCTCOUNT('Category Groups'[QuestionID]),'Category Groups'[Factor] = "GWB")<=20, "Lower QoWL",If(Calculate(AVERAGE('Survey Data'[Score])*DISTINCTCOUNT('Category Groups'[QuestionID]),'Category Groups'[Factor] = "GWB")>=24, "Higher QoWL","Average QoWL"))

Then the HWI assessment:

If(Calculate(AVERAGE('Survey Data'[Score])*DISTINCTCOUNT('Category Groups'[QuestionID]),'Category Groups'[Factor] = "HWI")<=9, "Lower QoWL",If(Calculate(AVERAGE('Survey Data'[Score])*DISTINCTCOUNT('Category Groups'[QuestionID]),'Category Groups'[Factor] = "HWI")>=12, "Higher QoWL","Average QoWL"

and so on for each category. Infuriating because this is the stuff I could do in Qlik (prob a bad word!) using the aggr function.

Any help would be appreciated

Ben

[ERD]

Hi @Bennyboysmith ,

no need to create so many measures, just use SWITCH. Something like this:

Assessment = 
VAR _measure = SELECTEDVALUE ( 'Table'[Measure] ) /* AVERAGE('Survey Data'[Score])*DISTINCTCOUNT('Category Groups'[QuestionID]) */
VAR _factor = SELECTEDVALUE ( 'Table'[Factor] )
RETURN
    SWITCH (
        TRUE (),
        _factor = "GWB" && _measure <= 20, "Lower QoWL",
        _factor = "GWB" && _measure >= 24, "Higher QoWL",
        _factor = "HWI" && _measure <= 9, "Lower QoWL",
        _factor = "HWI" && _measure >= 12, "Higher QoWL",
        "Average QoWL"
    )