cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sy898661 Member
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
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
tejaswidmello Established Member
Established Member

Re: Return a value in a column

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

sy898661 Member
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 Smiley Sad do you know of any way via DAX?

tejaswidmello Established Member
Established Member

Re: Return a value in a column

Hi @sy898661 

 

Try this measure.

 

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

Re: Return a value in a column

Hi @tejaswidmello ,

 

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 Smiley Sad 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

Highlighted
tejaswidmello Established Member
Established Member

Re: Return a value in a column

Can you share the screenshot?

Super User
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
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])
sy898661 Member
Member

Re: Return a value in a column

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

Helpful resources

Announcements
Virtual Launch Event

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.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 41 members 1,141 guests
Please welcome our newest community members: