Forum Discussion

Saxon10's avatar
Saxon10
Post Prodigy
5 years ago
Solved

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...
  • CNENFRNL's avatar
    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")