Forum Discussion

Bennyboysmith's avatar
Bennyboysmith
New Member
3 years ago

Help with DAX and measures

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:

 

FactorMeasureGWB MeasureHWI MeasureJCS MeasureCAW MeasureWCS MeasureSAW Measure
CAW8Lower QoWLHigher QoWLLower QoWLLower QoWLAverage QoWLAverage QoWL
GWB16.8Lower QoWLHigher QoWLLower QoWLLower QoWLAverage QoWLAverage QoWL
HWI12Lower QoWLHigher QoWLLower QoWLLower QoWLAverage QoWLAverage QoWL
JCS16Lower QoWLHigher QoWLLower QoWLLower QoWLAverage QoWLAverage QoWL
SAW4.4Lower QoWLHigher QoWLLower QoWLLower QoWLAverage QoWLAverage QoWL
WCS9.6Lower QoWLHigher QoWLLower QoWLLower QoWLAverage QoWLAverage 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:

 

FactorMeasureAssessment
CAW8Lower QoWL
GWB16.8Lower QoWL
HWI12Higher QoWL
JCS16Lower QoWL
SAW4.4Average QoWL
WCS9.6Average 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

1 Reply

  • ERD's avatar
    ERD
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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"
    )