Forum Discussion

sy898661's avatar
sy898661
Icon for Helper V rankHelper V
7 years ago
Solved

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!

Need Help
  • Cmcmahan's avatar
    Cmcmahan
    7 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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

     

    I got this output  

     

     

    Thanks,

    Tejaswi

    • sy898661's avatar
      sy898661
      Icon for Helper V rankHelper V
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi sy898661 

         

        Try this measure.

         

        Measure = Maxx(Sheet2, if(Sheet2[Sasles]=43658,Blank(),Sheet2[Sasles]))
         
        My output:
         
    • Cmcmahan's avatar
      Cmcmahan
      Icon for Resident Rockstar rankResident Rockstar
      7 years ago

      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.

    • Cmcmahan's avatar
      Cmcmahan
      Icon for Resident Rockstar rankResident Rockstar
      7 years ago

      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's avatar
        sy898661
        Icon for Helper V rankHelper V
        7 years ago

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