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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pierpa
Frequent Visitor

Excluding from a column values higher than 1000 and lower than -1000 using IF

Hello everyone, I have a little problem that I can't solve.

I have a table in which I calculate the delta between two values. This delta is shown as a percentage. Sometimes I have values higher than 1000 and lower than -1000, I would like to exclude them from the column.

Now i calculate the delta with a measure called Var2%, then I created a new measure called Var% Adj to filter the data.

I tried with nested IF

Var% ADJ =
IF ( [Var2%] > 1000; "ND"; IF ( [Var2%] <= -1000; "ND"; [Var2%] ) )

 

While it successfully discards values higher than 1000, the measure doesn't discard values lower than -1000

Var2%Var% ADJ
20444050003040530,0%ND
0,0%0,0%
24,9%24,9%
-4065,7%-4065,7%

 

Any suggestion? Thank you

1 ACCEPTED SOLUTION
vik0810
Resolver V
Resolver V

Hi @pierpa,

 

your measure Var2% is a percentage. That's why you must use 10 instead of 1000 in var% ADJ (1000% = 10).

 

Var% ADJ =
IF ( [Var2%] > 10; "ND"; IF ( [Var2%] <= -10; "ND"; [Var2%] ) )

 

View solution in original post

2 REPLIES 2
vik0810
Resolver V
Resolver V

Hi @pierpa,

 

your measure Var2% is a percentage. That's why you must use 10 instead of 1000 in var% ADJ (1000% = 10).

 

Var% ADJ =
IF ( [Var2%] > 10; "ND"; IF ( [Var2%] <= -10; "ND"; [Var2%] ) )

 

Thank You!!!

I feel really dumb, I was thinking about the original value. Thank you again!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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