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)
pawelj795's avatar
pawelj795
Icon for Post Prodigy rankPost Prodigy
6 years ago

v-kelly-msft 

Even with my above adjustments or with precisely your measure, it still only exlude transactions with trans type 0 or 9, not whole Index (ItemID)

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

    v-kelly-msft 
    It's possible to do what I want?