cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sentsara Regular Visitor
Regular Visitor

Tier value through DAX

Hi Team,

 

Need help to derive below below 5 Measures DAX Expression. 

RankNextTierUPRankNextTierUpMinValueNextTierBelowRankNextTierDownMaxValue

 

Requirement:

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

 

Dataset: Benchmark 

PlanIDMeasurekeySubMeasurekeySource_Measurement_YearSource_Reported_yearPercentileValueMinMaxRank
GRBCK 2019202010th_percentile0.548900.54891
GRBCK 2019202033th_Percentile0.61640.5490.61642
GRBCK 2019202066th_percentile0.68660.61650.68663
GRBCK 2019202090th_percentile0.73870.68670.73874
GRBCK 20192020Above90th_percentile10.738815

 

Dataset: CompletionFactor

PlanIDMeasureIDSubMeasureMeasurement_YearReporting_YearRunMonthTrendMonthForecastRate
GRBCK 2019202020190220.5555
GRBCK 2019202020190330.6555
GRBCK 2019202020190440.7055
GRBCK 2019202020190550.4211
GRBCK 2019202020190660.8792
GRBCK 2019202020190770.8802

Note: ForecastRate is calculated measure

 

All 5 DAX Expression should be in CompletionFactor

Expected output:

PlanIDMeasureIDSubMeasureMeasurement_YearReporting_YearRunMonthTrendMonthForecastRateRankNextTierUPRankNextTierUpMinValueNextTierBelowRankNextTierDownMaxValue
GRBCK 2019202020190220.5555230.616510.5489
GRBCK 2019202020190330.6555340.686720.6164
GRBCK 2019202020190440.7055450.738830.6866
GRBCK 2019202020190550.4211120.54910.5489
GRBCK 2019202020190660.8792550.738840.7387
GRBCK 2019202020190770.8802550.738840.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.

 

 

 

 

 

 

 

1 REPLY 1
Highlighted
vimal_parmar Frequent Visitor
Frequent Visitor

Re: Tier value through DAX

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 53 members 1,156 guests
Please welcome our newest community members: