topic Re: Hi, I am finiding difficulty in solving this expression using Dax in DAX Commands and Tips

Hi, I am finiding difficulty in solving this expression using Dax

ShaRaj — Sun, 08 Jan 2023 15:11:51 GMT

Expression :

if condition is positive,

then

a= x-min(x)/target(x)-Min(x)

for example X= 1,2,3,4,5

Target =10

Any help would be greatly appreciated.

Re: Hi, I am finiding difficulty in solving this expression using Dax

Greg_Deckler — Sun, 08 Jan 2023 16:03:24 GMT

ShaRaj Can you provide some more information on this. Are you saying you want to compute a for each value of x? So

x=1

1 - 1 / 10 - 1 = 1 - .1 - 1 = .9 - 1 = -.1

Or ?

Re: Hi, I am finiding difficulty in solving this expression using Dax

ShaRaj — Sun, 08 Jan 2023 17:15:40 GMT

yes,

Also I have different set of value for different category

for Example

PN Category Value Target
Positive Red 1 10
Positive Red 2 10
negative Red 3 10
negative Yellow 15 25
Negative Yellow 18 25
Negative Yellow 7 25

Re: Hi, I am finiding difficulty in solving this expression using Dax

Anonymous — Sun, 08 Jan 2023 20:16:28 GMT

To convert this formula to DAX, you can use the following expression:

a = IF( x > 0, (x - MIN(x)) / (target - MIN(x)), 0 )

This expression first checks if x is positive using the IF function. If it is positive, the formula calculates (x - MIN(x)) / (target - MIN(x)), and if it is not positive, it returns 0.

Remember me in prayers.

Farhad

Re: Hi, I am finiding difficulty in solving this expression using Dax

FreemanZ — Mon, 09 Jan 2023 00:16:28 GMT

hi ShaRaj

with the mentioned dataset, what result do you expect?

Re: Hi, I am finiding difficulty in solving this expression using Dax

ShaRaj — Mon, 09 Jan 2023 05:41:00 GMT

"Positive" is in text format

Re: Hi, I am finiding difficulty in solving this expression using Dax

ShaRaj — Mon, 09 Jan 2023 05:44:52 GMT

if PN is Positive do

A= x-min(x)/target(x)-Min(x)

else

A= x-min(x)/target(x)+Min(x)

This is the logic,For each i have to find new "A"

Re: Hi, I am finiding difficulty in solving this expression using Dax

Anonymous — Tue, 10 Jan 2023 09:11:37 GMT

Hi ShaRaj ,

Here are the steps you can follow：

1. Create calculated column.

logic = var _minx=MINX(ALL('Table'),[Value]) return IF( 'Table'[PN]="Positive", DIVIDE('Table'[Value] -_minx , 'Table'[Target] -_minx), DIVIDE('Table'[Value] -_minx , 'Table'[Target] +_minx))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: Hi, I am finiding difficulty in solving this expression using Dax

ShaRaj — Tue, 10 Jan 2023 10:13:18 GMT

hi ,

min of each category is what we need,not minimum of [value]

Re: Hi, I am finiding difficulty in solving this expression using Dax

Anonymous — Tue, 10 Jan 2023 13:48:58 GMT

Hello ShaRaj please try the same what Anonymous told you but use this formula instead

logic =
var _minx=CALCULATE(min(value),allexcept(table,[category]))
return
IF(
'Table'[PN]="Positive",
DIVIDE('Table'[Value] -_minx , 'Table'[Target] -_minx),
DIVIDE('Table'[Value] -_minx , 'Table'[Target] +_minx))