Need help with creating cut points for a categorical rating

[Anonymous]

I am looking to use a secondary table to generate a star rating. The data is given as a percentage between 0 and 100 rounded to the tenths place. There is a secondary table with the start and end of the range for each star rating. I am looking for a way to reference between the tables. Examples below. 

For example this is the data I have 

ID Number	Timely Initiation of care	Mobility	In/Out of Bed	Bathing	Less Pain
17000	96.6	79.6	68.2	76.3	65.6
17009	95	83.6	78.5	83.8	88.2
17013	99.2	77.9	76.3	86	91.3
17014	98.3	82.3	79.4	83.1	88.9
17016	99.6	85.5	84.9	87.8	94.2
17018	98	85.9	83.4	91.7	89.7
17020	98.2	93.6	82.7	87	93.6
17024	99.2	89.2	84.5	88.3	96.6
17025	97.2	78.1	73.3	76.1	78.3
17026	87.6	74.5	71.3	76.6	77.1
17027	99.7	84.8	87.2	91.8	84.5
17028	86.9	85.2	80.8	83.3	85.3
17034	98.5	83	82.6	89.1	88.6
17035	99.8	83.6	87.4	88.9	90.8
17037	98.6	83.2	85.1	86.8	94.4
17039	98.8	88.4	81.9	91.6	94.3
17048	98.6	84.2	76.5	90	94.2
17050	73.7	53.7	68.8	66.4	66.2
17051	97.8	73.6	73.9	78.6	80.9
17052	98.5	81.1	81	87.3	83
17053	99	86.8	82.1	91.1	85.9
17054	93.6	78.5	75.5	83	82.1
17055	96.7	77.5	78.1	83.7	87.8
17056	94.5	75.4	74.2	81.2	87.4
17058	99	80.1	79.8	86.2	85.3
17063	98.5	68.9	69.4	80.5	75.7
17066	93.3	71.1	76.3	76.4	76.8
17067	96.4	80.3	83.2	84.7	79.2
17069	98.9	92.2	86.5	92.7	95.9
17070	97.6	78.5	77.4	84.6	87.8
17071	98.8	87.8	83.6	92.7	88.6
17072	99	86.9	80.5	75.1	84.2

And this is the reference table with the ending of .1 being the lower bound of that star rating and .2 being the upper bound.

Star Rating	Timely Initiation of Care.1	Timely Initiation of Care.2	Mobility.1	Mobility.2	In/Out Bed.1	In/Out Bed.2	Bathing.1	Bathing.2	Less Pain.1	Less Pain.2
0.5	0	82.6	0	57	0	53.6	0	55.8	0	53.9
1	82.7	89	57.1	65	53.7	62.8	55.9	65.5	54	64.4
1.5	89.1	92.4	65.1	70	62.9	68.7	65.6	70.9	64.5	70.6
2	92.5	94.8	70.1	73.2	68.8	72.6	71	74.8	70.7	75.4
2.5	94.9	96.3	73.3	75.7	72.7	75.5	74.9	77.9	75.5	79
3	96.4	97.4	75.8	78	75.6	77.8	78	80.5	79.1	82.4
3.5	97.5	98.3	78.1	80.5	77.9	80.3	80.6	83.2	82.5	85.9
4	98.4	99	80.6	83.7	80.4	83.1	83.3	86.4	86	89.7
4.5	99.1	99.6	83.8	88.1	83.2	87.6	86.5	91	89.8	94.5
5	99.7	100	88.2	100	87.7	100	91.1	100	94.6	100

The goal is to assign a star rating for each category to each row in the initial data. Looking for a way to do this as a reference to this second table since that table is updated quarterly.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create measures

Measure_bathing = CALCULATE(MAX(Sheet5[Star Rating]),FILTER(Sheet5,MAX(Sheet4[Bathing])>=Sheet5[Bathing.1]&&MAX(Sheet4[Bathing])<=Sheet5[Bathing.2]))

Measure_bed = CALCULATE(MAX(Sheet5[Star Rating]),FILTER(Sheet5,MAX(Sheet4[In/Out of Bed])>=Sheet5[In/Out Bed.1]&&MAX(Sheet4[In/Out of Bed])<=Sheet5[In/Out Bed.2]))

Measure_care = CALCULATE(MAX(Sheet5[Star Rating]),FILTER(Sheet5,MAX(Sheet4[Timely Initiation of care])>=Sheet5[Timely Initiation of Care.1]&&MAX(Sheet4[Timely Initiation of care])<=Sheet5[Timely Initiation of Care.2]))

Measure_mobility = CALCULATE(MAX(Sheet5[Star Rating]),FILTER(Sheet5,MAX(Sheet4[Mobility])>=Sheet5[Mobility.1]&&MAX(Sheet4[Mobility])<=Sheet5[Mobility.2]))

Measure_pain = CALCULATE(MAX(Sheet5[Star Rating]),FILTER(Sheet5,MAX(Sheet4[Less Pain])>=Sheet5[Less Pain.1]&&MAX(Sheet4[Less Pain])<=Sheet5[Less Pain.2]))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

zz