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

     

selimovd's avatar
selimovd
Most Valuable Professional
5 years ago

Hey Saxon10 ,

 

can you explain your result a little better? I didn't understand when the result should be a yes or a no.

The first argument of EARLIER has to be a column reference, so you can't just combine a LEFT and SEARCH instead of a column.

 

But as I said I didn't understand what you want to do and why.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 
  • Saxon10's avatar
    Saxon10
    Post Prodigy
    5 years ago

    Thanks for your reply and sorry for the inconvenience.

     

    If country code has prefix only then it's dispatched within the city limit and if country code has prefix and suffix then it's dispatched out of the city limt for the same country so I am trying to identify what are item are dispatched with same country code.

     

     

    I create a addtional column which is does not contain the suffix after the country&code

     

    Country Code without "LL" = LEFT('Table'[Country & Code],SEARCH("-",'Table'[Country & Code]&"-")-1)
     
    Now I am trying to count the "item" and "Country Code without "LL" column and If count more than one then "Yes" and if not then "No".
     
    RESULT = IF(COUNTROWS(FILTER(ALL('Table'),'Table'[Item]=EARLIER('Table'[Item]) && 'Table'[Country Code without "LL"]=EARLIER('Table'[Country Code without "LL"])))>1,"YES","NO")
     
    But I would like to get the same result without creating addtional column.
     
    Can you please advise how can I do it?