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