COUNTIFS With two column

[Saxon10]

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 row)

I am trying to applying the following DAX but I am receving error "The first argument of EARLIER/EARLIEST is not a valid column reference in the earlier row context."

RESULT = COUNTROWS(FILTER(ALL('Table'),'Table'[Item]=EARLIER('Table'[Item]) && LEFT('Table'[Country & Code],SEARCH("-",'Table'[Country & Code]&"-")-1)=EARLIER(LEFT('Table'[Country & Code],SEARCH("-",'Table'[Country & Code]&"-")-1))))

DATA & RESULT:

Item	Country & Code	RESULT STATUS
123	DEL070	YES
123	DEL070-LL	YES
123	RA200	YES
123	RA200-LL	YES
456	TR030	YES
456	TR030-LL	YES
789	TR040	NO
789	TR050	NO
788	TR050-LL	NO
788	TR040-LL	NO
98745	RA400	NO
45632	RA090	NO

[CNENFRNL · Accepted solution]

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")

[Saxon10]

Thank you so much for your reply and solution for Excel and Power BI.

This is amazing. Thank you.

[selimovd]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Saxon10]

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?