Forum Discussion
New Measure-IF statement with 2 columns
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 ✨
Nabha-Ahmed , thanks so much , you are star😘
I am so happy that's work with you
you are welcome any time ,we,re here to help each other✨