Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
In Query Editor, split your Protocol column. Unpivot the resulting columns.
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
11 | |
9 | |
6 |