Forum Discussion

magutter's avatar
magutter
Regular Visitor
8 years ago

Custom Columns

I'm tring to create a custom column which uses  sum(Sales) but want to filter on another column call type where it shows everything EXCEPT Types that equal "JC" or "JD". Tried the following syntax bu...
magutter's avatar
magutter
Regular Visitor
8 years ago

Hi Dale

 

Thanks alot for your response. I know how to do it as a measure but what would you suggest when creating a custom column in the query editor. It would appear that custom columns uses "m" query language which I'm struggling with

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Which result do you want?

 

Best Regards,

Dale

  • magutter's avatar
    magutter
    Regular Visitor
    8 years ago

    Hi Dale

     

    I want it to get the NET figure for Sales but exclude TYPE of "JC" or "JD". I want those types to return a value of 0

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      8 years ago

      Hi magutter,

       

      You can try it like this:

      if [Sales] > 0 and [Type] <> "JC" and [Type] <> "JD" 
      then [Net Amount] 
      else 0

      Custom_Columns

       

      Best Regards,

      Dale