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

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 288 members 3,058 guests
Recent signins: