Forum Discussion

pawelj795's avatar
pawelj795
Icon for Post Prodigy rankPost Prodigy
6 years ago

Sum rows only with specific values in other column

Hi, I have database with inventory values like below. (sample data) Now, I want to create measure, which gonna calculate YTD value, but only for Items, which never had Transtype 0 or 9.
data protection
Need Help
Show and Tell
pawelj795's avatar
pawelj795
Icon for Post Prodigy rankPost Prodigy
6 years ago

Hi v-kelly-msft 

Shouldn't be TransType instead of ItemID in below measure and also OR instead of AND?

Column = 
var a=SEARCH(0,'Table'[ItemID],1,0)
var b=SEARCH(9,'Table'[ItemID],1,0)
Return
IF(a=0&&b=0,1,0)

 

Column =
VAR A = SEARCH(0;WH_Invent_Trans[TransType];1;0)
VAR B = SEARCH(9;WH_Invent_Trans[TransType];1;0)

RETURN
IF (A=0 || B=0;1;0)
v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi pawelj795 ,

 

a=0 means there's no 0 in the column, so is b ,so a=0&&b=0 means there's no 0 or 9 in column ID, you said all the rows which contains 0 or 9 in column ID and TransType should all be excluded...so in my measure I have excluded all the posibilities.I have checked my result,it satisfies your need,where do you find not work?

 

Best Regards,
Kelly
 
Did I answer your question? Mark my post as a solution!