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
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
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

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