Forum Discussion
Saxon10
5 years agoPost Prodigy
COUNTIFS With two column
Hi, I am trying to count based on the two columns here item and country& code. If same item has same country & code then "Yes" otherwise return "No". (Please ignore -LL while counting the r...
- 5 years ago
You may want to try in a calculated column,
RESULT_CC = IF( COUNTROWS( FILTER( DS, DS[Item] = EARLIER( DS[Item] ) && CONTAINSSTRING( DS[Country & Code], SUBSTITUTE( EARLIER( DS[Country & Code] ), "-LL", "" ) ) ) ) > 1, "Yes", "No" )I personally prefer solutions in PQ and Excel, considering that DAX is not so competent in coping with string transformation.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc0xDoAgDIXhu3TG5AlFymiiGxNxI9z/GhJMVRj/L31pKbRaR4aOMyGAqhlhSelnebfA3HrCfmt0ZTjMrSdB4kOMsb22aH8T0YlSlMC+f2e8r5ztgtik3g==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Country & Code" = _t]), Custom1 = let cols = {"Item", "Country & Code"} in Table.RemoveColumns(Table.Group(Source, cols, {"ar", each _}, 0, (x,y) => Number.From(not(x[Item]=y[Item] and Text.Contains(Text.Replace(y[#"Country & Code"], "-LL", ""), Text.Replace(x[#"Country & Code"], "-LL", ""))))), cols), Custom2 = Table.TransformColumns(Custom1, {"ar", each Table.AddColumn(_, "RESULT_PQ", (x) => if Table.RowCount(_)>1 then "YES" else "NO")}), #"Expanded ar" = Table.ExpandTableColumn(Custom2, "ar", {"Item", "Country & Code", "RESULT_PQ"}, {"Item", "Country & Code", "RESULT_PQ"}) in #"Expanded ar"Still, Excel formula, our oldie but goodie, does the trick with ease; the DAX calculated column solution is, to some extent, a verbose copy of it!
=IF(COUNTIFS([Item],[@Item],[Country & Code],SUBSTITUTE([@[Country & Code]],"-LL","")&"*")>1,"YES","NO")
CNENFRNL
5 years agoCommunity Champion
You may want to try in a calculated column,
RESULT_CC =
IF(
COUNTROWS(
FILTER(
DS,
DS[Item] = EARLIER( DS[Item] )
&& CONTAINSSTRING( DS[Country & Code], SUBSTITUTE( EARLIER( DS[Country & Code] ), "-LL", "" ) )
)
) > 1,
"Yes",
"No"
)
I personally prefer solutions in PQ and Excel, considering that DAX is not so competent in coping with string transformation.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc0xDoAgDIXhu3TG5AlFymiiGxNxI9z/GhJMVRj/L31pKbRaR4aOMyGAqhlhSelnebfA3HrCfmt0ZTjMrSdB4kOMsb22aH8T0YlSlMC+f2e8r5ztgtik3g==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Country & Code" = _t]),
Custom1 = let cols = {"Item", "Country & Code"} in Table.RemoveColumns(Table.Group(Source, cols, {"ar", each _}, 0, (x,y) => Number.From(not(x[Item]=y[Item] and Text.Contains(Text.Replace(y[#"Country & Code"], "-LL", ""), Text.Replace(x[#"Country & Code"], "-LL", ""))))), cols),
Custom2 = Table.TransformColumns(Custom1, {"ar", each Table.AddColumn(_, "RESULT_PQ", (x) => if Table.RowCount(_)>1 then "YES" else "NO")}),
#"Expanded ar" = Table.ExpandTableColumn(Custom2, "ar", {"Item", "Country & Code", "RESULT_PQ"}, {"Item", "Country & Code", "RESULT_PQ"})
in
#"Expanded ar"
Still, Excel formula, our oldie but goodie, does the trick with ease; the DAX calculated column solution is, to some extent, a verbose copy of it!
=IF(COUNTIFS([Item],[@Item],[Country & Code],SUBSTITUTE([@[Country & Code]],"-LL","")&"*")>1,"YES","NO")
- Saxon105 years agoPost Prodigy
Thank you so much for your reply and solution for Excel and Power BI.
This is amazing. Thank you.