Tier value through DAX

[sentsara]

Hi Team,

Need help to derive below below 5 Measures DAX Expression. 

Rank

NextTierUPRank

NextTierUpMinValue

NextTierBelowRank

NextTierDownMaxValue

Requirement:

There is 2 Datasets name:Benchmark and CompletionFactor with no relationship in PowerBI Model

Dataset: Benchmark 

PlanID	Measurekey	SubMeasurekey	Source_Measurement_Year	Source_Reported_year	Percentile	Value	Min	Max	Rank
GR	BCK		2019	2020	10th_percentile	0.5489	0	0.5489	1
GR	BCK		2019	2020	33th_Percentile	0.6164	0.549	0.6164	2
GR	BCK		2019	2020	66th_percentile	0.6866	0.6165	0.6866	3
GR	BCK		2019	2020	90th_percentile	0.7387	0.6867	0.7387	4
GR	BCK		2019	2020	Above90th_percentile	1	0.7388	1	5

Dataset: CompletionFactor

PlanID	MeasureID	SubMeasure	Measurement_Year	Reporting_Year	RunMonth	TrendMonth	ForecastRate
GR	BCK		2019	2020	201902	2	0.5555
GR	BCK		2019	2020	201903	3	0.6555
GR	BCK		2019	2020	201904	4	0.7055
GR	BCK		2019	2020	201905	5	0.4211
GR	BCK		2019	2020	201906	6	0.8792
GR	BCK		2019	2020	201907	7	0.8802

Note: ForecastRate is calculated measure

All 5 DAX Expression should be in CompletionFactor

Expected output:

PlanID	MeasureID	SubMeasure	Measurement_Year	Reporting_Year	RunMonth	TrendMonth	ForecastRate	Rank	NextTierUPRank	NextTierUpMinValue	NextTierBelowRank	NextTierDownMaxValue
GR	BCK		2019	2020	201902	2	0.5555	2	3	0.6165	1	0.5489
GR	BCK		2019	2020	201903	3	0.6555	3	4	0.6867	2	0.6164
GR	BCK		2019	2020	201904	4	0.7055	4	5	0.7388	3	0.6866
GR	BCK		2019	2020	201905	5	0.4211	1	2	0.549	1	0.5489
GR	BCK		2019	2020	201906	6	0.8792	5	5	0.7388	4	0.7387
GR	BCK		2019	2020	201907	7	0.8802	5	5	0.7388	4	0.7387

Explanation for each DAX Measure 

1) Rank: ForecastRate (Measure) from CompletionFactor Dataset needs to compare with benchmark dataset between MIN and MAX Value and get the Rank

2) NextTierUpRank :  based on the currentRank what is the next level incremental rank..

if its reaches 5 then the max rank will be 5 only

3) NextTierBelowRank: based on the currentRank what is the next level decrease rank.

if it is reaches 1 then it should be 1 only.

4) NextTierUpMinValue: based on NextTierUpRank get the Min Value from Benchmark Dataset

5) NextTierDownMaxValue: based on NextTierBelowRank get the MaxValue from Benchmark Dataset.

[Anonymous]

Rank = CALCULATE(DISTINCT(Benchmark[Rank]),FILTER(Benchmark,SUM(CompletionFactor[ForecastRate])>Benchmark[Min] && SUM(CompletionFactor[ForecastRate])<=Benchmark[Max]))

NextTierUpRank = IF([Rank]>=5,[Rank],[Rank]+1)

NextTierBelowRank = IF([Rank]<=1,[Rank],[Rank]-1)

NextTierDownMaxValue = LOOKUPVALUE(Benchmark[Max],Benchmark[Rank],[NextTierBelowRank])

NextTierUpMinValue = LOOKUPVALUE(Benchmark[Min],Benchmark[Rank],[NextTierUpRank])