compare the value with the range which exists in second dataset and take the minimum tier

[sentsara]

Hello!

Need to derive using DAX Expression for the below business rule:

Dataset Name 1: CompetitorPerformance

Dataset Name 2: Tier

Competitor Performance:

Tier:

Need to take the "StateReportedMeasureRate" value from the competitorPerformance dateset and compare it with the "cutpoints" range  in tier dataset. we should get the minimum range tier result

For example:

StateReportedMeasureRate for CCSNULL is 55.47% 

expected output tier value should be (Through DAX) : 3

Points to consider:

if the stateReported value > 1 tier cutpoints  then the output sould be 1

if the stateReported value  < 5 tier cutpoints  then the output sould be 5

Key column for both the datasets is Measurejoiner

Dax finaloutput should be the Tier value from Tier Dataset

[Anonymous]

[Competitor Tier] :=
var __oneCompetitorVisible =
	COUNTROWS(
		SUMMARIZE(
			'Competitor Performance',
			'Competitor Performance'[Competitors],
			'Competitor Performance'[Measure]
		)
	) = 1
var __measureRate =
	MAX( 'Competitor Performance'[StateReportedMeasureRate] )
var __measure =
	MAX( 'Competitor Performance'[Measure] )
var __maxCutpoints =	
	CALCULATE(
		MAX( Tier[cutpoints] ),
		Tier[MeasureJoiner] = __measure,
		ALL( Tier )
	)
var __tier =
	var __tierHelper =
		CALCULATE(
			MIN( Tier[cutpoints] ),
			Tier[MeasureJoiner] = __measure,
			Tier[cutpoints] <= __measureRate
			ALL( Tier )
		)
	return
	if( ISBLANK(__tierHelper),
		__maxCutpoints,
		__tierHelper
	)
return	
	if( __oneCompetitorVisible, __tier )

[sentsara]

Hello

Sorry for the confusion.

Assume if statereportedmeasurerate is 60% for BCSNULL Measurejoiner then the tier should return 1

Assume if statereportedmeasurerate is 50% for BCSNULL Measurejoiner then the tier should return 5

Assume if statereportedmeasurerate is 53% for BCSNULL Measurejoiner then the tier should return 3

This make sense?

[Anonymous]

---

@sentsara wrote:
Hello

Sorry for the confusion.

Assume if statereportedmeasurerate is 60% for BCSNULL Measurejoiner then the tier should return 1

WHY SHOULD IT RETURN 1? Please, state the rule that enables me to say the output should be 1. For instance, you should say something like this: For StateReportedMeasureRate of 60% (=.60), it should return 1 because -  and I'm now making this up - the number of rows in the other table where points < 0.6 is 1. Is this understandable?

Assume if statereportedmeasurerate is 50% for BCSNULL Measurejoiner then the tier should return 5

WHY SHOULD IT RETURN 5?

Assume if statereportedmeasurerate is 53% for BCSNULL Measurejoiner then the tier should return 3

WHY SHOULD IT RETURN 3?

I need to know HOW TO CALCULATE the output for ANY INPUT, not what the output will be for some specific input. It's like when you try to teach a child how numbers are added. If you tell them that 2 + 2 = 4 and 10 + 1 = 11, they will learn this by heart without any understanding how to perform addition and will not be able to answer what the result of 2 + 3 is.

---

[Anonymous]

OK, I know the rule now.

It should return the MINIMUM rank from among all the entries which have measure >= statereportedmeasurerate with some boundary conditions... or something like that.

Please, bear with me.

Best

Darek

[Anonymous]

This is probably a pretty easy problem to write a DAX measure for but you've made it rather obscure. The rules are unclear. Please state the rules correctly.

I'll give you an example of what I'm talking about.

You say:

Need to take the "StateReportedMeasureRate" value from the competitorPerformance dateset and compare it with the "cutpoints" range in tier dataset. we should get the minimum range tier result

For example:

StateReportedMeasureRate for CCSNULL is 55.47% 

expected output tier value should be (Through DAX) : 3 (completely unclear why)

Points to consider:

if the stateReported value > 1 tier cutpoints  then the output sould be 1 

if the stateReported value  < 5 tier cutpoints  then the output sould be 5

The above is contradictory. If the stateReportedMeasureRate value > 1 tier cut points and < 5 tier cut points, then both rules are satisfied and the output is contradictory as it can't be 1 and 5 at the same time.

Can you please make sure that the rules are clear as possible? If you don't have clear, understandable rules, nobody will even try to solve your problem.

Thanks.

Best

Darek