How to apply multiple logical layers to a Measure to create another Measure in DAX

Question

My data is similar to the following:

Type   Compliant   Non Compliant
A      1           0
B      0           1
C      1           0

I have the following measure that returns the percentage of compliance:

sum(Table[Compliance])/(sum(Table[Compliance])+sum(Table[NonCompliant]))

which gives something like this:

Type    Compliance %
A       74.45
B       53.36
C       29.88

Great so far, but now I want to categorize the percentage by % range AND type:

Type    Compliance %   Class
A       74.45          5
B       53.36          3
C       29.88          1

Currently, I have one measure for each type, because the thresholds for each percentage are different:

Type A Category= CALCULATE(
SWITCH (
    TRUE (),
    [MeasureCompliancePCT] <= 0.25, 1,
    [MeasureCompliancePCT] <= 0.50, 2,
    [MeasureCompliancePCT] <= 0.61, 3,
    [MeasureCompliancePCT] <= 0.80, 4,
    5),Table[Type]="A"
)

While this works, these pivoted measures are hard to work with, so I tried to make a nested SWITCH calculation:

Class= CALCULATE(SUM(
SWITCH (
    TRUE (),
    [MeasureCompliancePCT] <= SWITCH(TRUE(), Table[Type]="A", -1,
                                             Table[Type]="B", -1),0,
    [MeasureCompliancePCT] <= SWITCH(TRUE(), Table[Type]="A", 0.25,
                                             Table[Type]="B", 0.49),1,
    [MeasureCompliancePCT] <= SWITCH(TRUE(), Table[Type]="A", 0.50,
                                             Table[Type]="B", 0.66),2,
    [MeasureCompliancePCT] <= SWITCH(TRUE(), Table[Type]="A", 0.61,
                                             Table[Type]="B", 0.78),3,
    [MeasureCompliancePCT] <= SWITCH(TRUE(), Table[Type]="A", 0.80,
                                             Table[Type]="B", 0.86),4,
    5)))

But so far, no combination of formulas that I have tried avoids the dreaded "single value cannot be determined" error.

I know this must be possible in the language, but there's some element that I'm missing. If only the table relationship supported a BETWEEN operator...

Kyle Hale Kyle Hale · Accepted Answer · 2015-12-19T03:37:54

Rather than try to implement an entire table in a measure, a little bit of modeling will go a long way.

Create two more tables:

One, with just the types -

Type
A
B
C

And one with your type class bands -

Type    BandBegin   BandEnd Class
A       -1          0.25    1
A       0.25        0.5     2
A       0.5         0.61    3
A       0.61        0.8     4
A       0.8         10      5
B       -1          0.49    1
B       0.49        0.66    2
B       0.66        0.78    3
B       0.78        0.86    4
B       0.86        10      5

Join up your Compliance table and the Classes table to the Types table on Type.

Now you just need to create two measures:

CompliancePct:=SUM( Compliance[Compliant] ) / COUNTROWS( Compliance )

CorrectBand := CALCULATE( MAX(Classes[Class])
 , FILTER ( 
      Classes,
      Classes[BandBegin] <= Compliance[CompliancePct]
      && Classes[BandEnd] > Compliance[CompliancePct] 
      ) 
 )

How to apply multiple logical layers to a Measure to create another Measure in DAX

1 Answers