New Measure-IF statement with 2 columns

[longlongisi]

Hi All

I would like to have a meaure, if " Accessories_Treats" is Treats, & "Item Number " is duplicate, then =1.

For 2134, only 1 row is " Treats", then it is 0, whole 2345, 2 rows are treats, then =1.

May i have your help how i can get it?🙏

Thanks 

[longlongisi]

@Nabha-Ahmed , thanks so much , you are star😘

[Nabha-Ahmed]

I am so happy that's work with  you 

you are welcome any time ,we,re here to help each other✨

[Nabha-Ahmed]

I am so happy that's work with  you 

you are welcome any time ,we,re here to help each other✨

[Nabha-Ahmed · Accepted solution]

Hi   

you can flag rows where an Item Number has more than one "Treats" row and mark those "Treats" rows with 1(otherwise 0).
Below are two options: a measure (dynamic, recommended in visuals) and a calculated column (stored at refresh).

Replace YourTable with your actual table name.

Measure (recommended for visuals):

 

Treats_Duplicate_Flag_Measure =
VAR ThisItem = SELECTEDVALUE(YourTable[Item Number])
VAR ThisRowIsTreat = SELECTEDVALUE(YourTable[Accessories_Treats]) = "Treats"
VAR NumTreatsForItem =
CALCULATE(
COUNTROWS(YourTable),
FILTER(
ALL(YourTable),
YourTable[Item Number] = ThisItem
&& YourTable[Accessories_Treats] = "Treats"
)
)
RETURN
IF(ThisRowIsTreat && NumTreatsForItem > 1, 1, 0)

• Put Item Number, Amount and this measure in a table visual — each row where Accessories_Treats = "Treats"and the item has more than one Treats will show 1.

  Calculated column (if you prefer a stored column):

  Treats_Duplicate_Flag_Measure =
  VAR ThisItem = SELECTEDVALUE(YourTable[Item Number])
  VAR ThisRowIsTreat = SELECTEDVALUE(YourTable[Accessories_Treats]) = "Treats"
  VAR NumTreatsForItem =
  CALCULATE(
  COUNTROWS(YourTable),
  FILTER(
  ALL(YourTable),
  YourTable[Item Number] = ThisItem
  && YourTable[Accessories_Treats] = "Treats"
  )
  )
  RETURN
  IF(ThisRowIsTreat && NumTreatsForItem > 1, 1, 0)

  • This column is computed at data refresh and can be used for filtering/grouping without recomputation at report runtime.

    Thanks ✨🌹

    put kudo ✨

@longlongisi