Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sentsara
Helper II
Helper II

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

Hello!

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

Dataset Name 1: CompetitorPerformance

Dataset Name 2: Tier

 

Competitor Performance:

comp.PNG

 

Tier:

tier.PNG

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

 

5 REPLIES 5
Anonymous
Not applicable

[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
Helper II
Helper II

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
Not applicable


@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
Not applicable

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
Not applicable

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors