topic Re: Need help in DAX formula in Developer

Need help in DAX formula

manojk_pbi — Thu, 22 Feb 2024 14:25:16 GMT

Hi Friend,

I have one requirement where in we have some deliver percentage values. I need to recalculate these values using the targets defined to derive normalized values. Providing below a simple example with data. I am not expert in DAX nor in Modeling. Please suggest how this can be achieved.

My actual value for the Dec'23 is 86%, have to derive the normalized value using the below Threshold table.

Calculation would be something like below, expected result for 86% is 106%

for 85% it is 100% as per threshold, we need calculate what will the value of 1%.

2023

Threshold

(0%)

(50%)

(100%)

(150%)

Delivery

Thanks in advance,

Re: Need help in DAX formula

Greg_Deckler — Thu, 22 Feb 2024 14:38:51 GMT

@manojk_pbi So why exactly does 86% become 106%? How does that work exactly?

Re: Need help in DAX formula

manojk_pbi — Thu, 22 Feb 2024 14:57:04 GMT

The calculation goes like below,

86 is greater than 85 & less than 93, so 86 is one percent more than the threshold value so we need to calculate the converstion of 1% into the threshold.

ie. 93-85 = 8

150-100=50

Per 1% = (50/8) which is 6.25

there fore, 86% is equal to 106.2%

Re: Need help in DAX formula

Greg_Deckler — Thu, 22 Feb 2024 15:13:05 GMT

@manojk_pbi JC, glad you explained it because I would have never figured that out from the information provided. Do this (PBIX is attached)

Measure = VAR __Value = [December] VAR __ThreshMin = MAXX(FILTER('Thresholds', [Value] <= __Value), [Attribute]) VAR __ThreshMinVal = MAXX(FILTER('Thresholds', [Value] <= __Value), [Value]) VAR __ThreshMax = MAXX(FILTER('Thresholds', [Value] > __Value), [Attribute]) VAR __ThreshMaxVal = MAXX(FILTER('Thresholds', [Value] > __Value), [Value]) VAR __Result = SWITCH( TRUE(), __ThreshMax = BLANK(), __Value, __Value = __ThreshMax, __Value, DIVIDE( __ThreshMax - __ThreshMin, __ThreshMaxVal - __ThreshMinVal) * (__Value - __ThreshMinVal ) + __ThreshMin ) RETURN __Result

Re: Need help in DAX formula

manojk_pbi — Thu, 22 Feb 2024 15:56:28 GMT

Thanks @Greg_Deckler for your quick solution. This will help.

Can you suggest how the same can be extended when we have values in table like below across different years.

	Year	0	50	100	150
Threshold	2023	72	78	85	93
Threshold	2024	72	75	80	90

MonthYear	Delivery%
Dec-23	86
Nov-23	84
Oct-23	83

Re: Need help in DAX formula

Greg_Deckler — Thu, 22 Feb 2024 16:06:14 GMT

@manojk_pbi I would still unpivot the last four columns of that table. Probably create a Year column in your Delivery table and relate that to your thresholds which should also have a straight Year column. After that, things should be very straight-forward.

Re: Need help in DAX formula

manojk_pbi — Thu, 22 Feb 2024 16:15:33 GMT

Hi @Greg_Deckler , I am new to DAX queries and i am not sure how the table links works. Please could you create a sample for me if it doen't take much time. This will be of great help.

Please suggest me some materials for better understanding of DAX queries.

Re: Need help in DAX formula

Greg_Deckler — Thu, 22 Feb 2024 16:32:46 GMT

@manojk_pbi Updated. See attached PBIX.

Measure = VAR __Value = MAX('Delivery'[Delivery%]) VAR __ThreshMin = MAXX(FILTER('Thresholds', [Value] <= __Value), [Attribute]) VAR __ThreshMinVal = MAXX(FILTER('Thresholds', [Value] <= __Value), [Value]) VAR __ThreshMax = MAXX(FILTER('Thresholds', [Value] > __Value), [Attribute]) VAR __ThreshMaxVal = MAXX(FILTER('Thresholds', [Value] > __Value), [Value]) VAR __Result = SWITCH( TRUE(), __ThreshMax = BLANK(), __Value, __Value = __ThreshMax, __Value, DIVIDE( __ThreshMax - __ThreshMin, __ThreshMaxVal - __ThreshMinVal) * (__Value - __ThreshMinVal ) + __ThreshMin ) RETURN __Result

Re: Need help in DAX formula

manojk_pbi — Fri, 23 Feb 2024 05:27:03 GMT

Thanks @Greg_Deckler for this solution.

Re: Need help in DAX formula

manojk_pbi — Tue, 26 Mar 2024 00:08:05 GMT

Hi @Greg_Deckler ,

I noticed something strange when the value is more than threshold value eg : in the sample we have taken 93% is equal to 150% but if value is 94 then it should return 150% rather it is returning 9400%. How this can be tweaked ? Same will be applied to lower limit as well.