Forum Discussion
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 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."
| 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 |
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")
4 Replies
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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovicThanks 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?
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")Thank you so much for your reply and solution for Excel and Power BI.
This is amazing. Thank you.
