cancel
Showing results for
Did you mean:
Highlighted
Member

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

Accepted Solutions
Super User

Re: Return a value in a column

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])`
8 REPLIES 8
Established Member

Re: Return a value in a column

Please try using Conditional colum approach this way:

-  hit on Edit queries

- Conditional Column

- Look at below screesnhot

- hit ok

-apply and close

I got this output

Thanks,

Tejaswi

Member

Re: Return a value in a column

@tejaswidmello 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?

Established Member

Re: Return a value in a column

Try this measure.

Measure = Maxx(Sheet2, if(Sheet2[Sasles]=43658,Blank(),Sheet2[Sasles]))

My output:

Member

Re: Return a value in a column

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

Established Member

Re: Return a value in a column

Can you share the screenshot?

Super User

Re: Return a value in a column

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.

Super User

Re: Return a value in a column

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])`
Member

Re: Return a value in a column

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

Announcements

Power BI Super User, Greg Deckler, explains

Power Platform Summit North America

Register by September 5 to save \$200

Back to School Contest

Engage and empower students with Power BI!

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 6 members 1,311 guests
Recent signins: