How to filter and count rows that contains a value in another column.

Anonymous — Fri, 13 Mar 2020 21:11:07 GMT

I have the a table called attck_table:

attck_table
Attack Name	Protocol	Action
Attack 1	http,citrix	Blocked
Attack 2	http,oracle	Blocked
Attack 3	ipv4, dns	Blocked
Attack 4	citrix	Blocked
Attack 5	irc	Blocked
Attack 6	ipv4,http,citrix	Blocked
Attack 7	dns	Blocked
Attack 8	imap,smtp	Blocked
Attack 9	http	Blocked
Attack 10	netbios-ss	Blocked
Attack 11	http,ssl,citrix	Blocked

I already created another table with pre-defined attack name values (manualy) and then I ve created an DAX Function for the "Total" Colum. The DAX Function is exactly that:

total =

CALCULATE(COUNTA(attck_table[Column1.protocol]),FILTER(ALL(attck_table),attck_table[Column1.protocol]='desired_table'[protocol]))

It is reasonably working, but the problem is that it is bring it just the columns that have the exact value, and as you can see, we have "comma" with more values.

desired_table
Protocol	Total
http	1
citrix	1
oracle	0
ipv4	0
irc	1
dns	1
imap	0
smtp	0
netbios-ss	1
ssl	0

What I want is to use something like "contain", "match", "like" or may be, use some wildcard to bring me all rows that have the value. Here the desired result:

desired_table
Protocol	Total
http	5
citrix	4
oracle	1
ipv4	2
irc	1
dns	2
imap	1
smtp	1
netbios-ss	1
ssl	1

I know that I can work also with variables poiting to 'desired_table'[Protocols] , but I got the same problem:

Do you guys have any idea?

Thank you so much.

Lucas

Re: How to filter and count rows that contains a value in another column.

Greg_Deckler — Fri, 13 Mar 2020 21:14:34 GMT

In Query Editor, split your Protocol column. Unpivot the resulting columns.

topic How to filter and count rows that contains a value in another column. in DAX Commands and Tips

How to filter and count rows that contains a value in another column.

Re: How to filter and count rows that contains a value in another column.