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
sy898661
Helper V
Helper V

Return a value in a column

Hi!

 

I'm sorry in advance if this is a dumb question but I was wondering if someone could help with my syntax:

 

Generation Duration 2 = IF('board-Query'[Generation Duration] = 43658, "", 'board-Query'[Generation Duration])

I am basically just trying to get rid of the 43658 value that is populating a large portion of my Generation Duration column (due to how we calculated that column) so i can find the real average/stdev. Right now, since there is the 43658 value for so many rows, the avg and stdev is WAY off what it actually should be.

 

So I just want to say, if the value in Generation Duration column = 43658, return BLANK, if it is any other value, return whatever the value is

 

Thank you!

1 ACCEPTED SOLUTION

I would use this for your average calculation:

AverageWithout43568s = CALCULATE(AVERAGE('board-Query'[Generation Duration]), 'board-Query'[Generation Duration] <> 43658)

And replace the average with whatever calculation you want to do for other measures.

 

If you really need a calculated column that shows non-43568 values, you could edit your original formula for a calculated column:

Generation Duration 2 = IF('board-Query'[Generation Duration] = 43658, BLANK(), 'board-Query'[Generation Duration])

You could also clean that up a bit more since IF defaults to null if you don't provide an alternate value:

Generation Duration 3 = IF('board-Query'[Generation Duration] <>  43658, 'board-Query'[Generation Duration])

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

hi @sy898661 

 

Please try using Conditional colum approach this way:

 

-  hit on Edit queries 

- Conditional Column

- Look at below screesnhot

- hit ok

-apply and close

Capture 14.PNG

 

I got this output  

Capture 15.PNG

 

 

Thanks,

Tejaswi

I would use this for your average calculation:

AverageWithout43568s = CALCULATE(AVERAGE('board-Query'[Generation Duration]), 'board-Query'[Generation Duration] <> 43658)

And replace the average with whatever calculation you want to do for other measures.

 

If you really need a calculated column that shows non-43568 values, you could edit your original formula for a calculated column:

Generation Duration 2 = IF('board-Query'[Generation Duration] = 43658, BLANK(), 'board-Query'[Generation Duration])

You could also clean that up a bit more since IF defaults to null if you don't provide an alternate value:

Generation Duration 3 = IF('board-Query'[Generation Duration] <>  43658, 'board-Query'[Generation Duration])

Oh yes thank you!! I used Generation Duration 3 and it works!!

I would use this for your average:

AverageWithout43568s = CALCULATE(AVERAGE('board-Query'[Generation Duration]), 'board-Query'[Generation Duration] <> 43658)

And replace the average with whatever calculation you want to do for other measures.

@Anonymous omg this would be so perfect BUT I just checked and unfortunately I do not have permission to edit the query 😞 do you know of any way via DAX?

Anonymous
Not applicable

Hi @sy898661 

 

Try this measure.

 

Measure = Maxx(Sheet2, if(Sheet2[Sasles]=43658,Blank(),Sheet2[Sasles]))
 
My output:
 
Capture 20.PNG

Hi @Anonymous ,

 

Now it is just populating the entire column with 68-the max value that is not 43658, instead of copying over the data from the other column

 

what the heck is going on 😞 I am **bleep** near 100% positive I typed it the same way you did--I wonder why it worked for you but isnt working for me

Anonymous
Not applicable

Can you share the screenshot?

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.