countif([columnA],[@columnB]) in DAX or M?

[alex1alex]

Title says most of it...
I'd like the DAX or M equivalent of countif([columnA],[@columnB])
(where I'm getting a count of how many times the cell in columnB appears in columnA)

(As an aside, In the end, what I'm really looking to do is filter out any rows where @ColumnB isn't in ColumnA. I'm using the PATH function and it doesn't like for you to include rows where the parent_columnName isn't in ID_columnName)

Thanks,
Alex

 

[olivierhbh]

Hello Alex,

Here is what I use: =IF(IFERROR(SEARCH([value to find], [column to search in]), 0), 1, 0), will return 1 if found, 0 if not. So you want all the 0s.

I hope it helps.

Olivier.

 

[alex1alex]

Hi Oliver,

I think that's comparing columnA = columnB for just the individual rows.

(the equivalent of =COUNTROWS(FILTER(theTable, [columnA ]=[columnB]) )

So, not quite what I'm looking for.

Thanks for trying though!

-Alex

 

[alex1alex]

AH!
I was looking for this:
=COUNTROWS(FILTER(theTable, EARLIER([columnA])=[columnB]))

Where 1 is returned if the current row columnA is found anywhere in columnB and (blank) is returned if it's not.

I'd forgotten "EARLIER is Probably Best Understood as CURRENTROW" via: http://www.powerpivotpro.com/2013/07/writing-a-subtotal-calc-column-aka-the-simplest-use-of-the-earlier-function/

PS: I'd be happy to see a M/PowerQuery version so that I can do the clean up in one step

 

[ImkeF]

Have a look if this is it:

let
Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ColA", type text}, {"ColB", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([ColA],[ColB]) = true then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1))
in
#"Filtered Rows"

 

[miguel.escobar]

Hey Alex,

The equivalent of EARLIER is shown here
ABC Analysis with Power Query - Powered Solutions

You can compare that one to this one:
ABC Classification – Dax Patterns

That's an M vs DAX Scenario. IMHO I prefer Power Query for it.

 

[alex1alex]

Hi Imke,
I'm afraid that this also yields the same results where the comparison is just between the two columns on the same row.
Thanks for the suggestion though,
Alex

 

[alex1alex]

For M, this works ( although it could probably be cleaned up...the important part is in bold). I got the hint from miguel's article.
I hope the conversion between list and table can be done cleaner. Also, I need to run go through this a few times so I'd like to make it into a function.

let


Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ColA = List.Buffer( Source[ColA]),
ColB = List.Buffer( Source[ColB]),
DistinctAmounts = List.Distinct( ColB ),
Exclude = List.Difference(DistinctAmounts ,ColA),
#"Table from List1" = Table.FromList(Exclude, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
S2=Source,
Merge = Table.NestedJoin(S2,{"ColB"},#"Table from List1",{"Column1"},"NewColumn"),
#"Expand NewColumn" = Table.ExpandTableColumn(Merge, "NewColumn", {"Column1"}, {"NewColumn.Column1"}),
#"Filtered Rows" = Table.SelectRows(#"Expand NewColumn", each [NewColumn.Column1] = null)
in
#"Filtered Rows"

 

[miguel.escobar]

Source[ColA] would yield a list with all the values from that ColumnA. You could use it and then use List.Select do to a count operation over that filtered list

 

[ImkeF]

Hi Alex,
Now I understand, sorry - have been too quick on this.

You’ll find the version with the function & a “slimmed” alternative in this file (too much to explain as it includes a new table as well - just ask if needed):
https://onedrive.live.com/edit.aspx...FF!10820&parId=DE165DDF5D02DAFF!107&app=Excel

Depending on if the result of the first round should restrict the result of the following rounds you take the AND (yes) or the OR (no)-version.