Forum Discussion
count if with search
Hi all,
i'm trying to tackle a calculation i need. I've tried to apply several formulas found here, but they're not working in my case.
i've a table "B", with a column of "code". I've to check for each row of "code" if these are contained in table "A", in the column "all-codes", which contain aggregated values for this codes. After this I can perform in another formula a text feedback (ie. found/not found).
I can do this in excel, with a simple countif(tableA!code;"*"&tableB!code&",") (I need the comma to avoid a wrong contain check).
can you please help me?
best
Hi Anonymous,
You can try to add a calculate column to 'table A' to count records which included in current row:
Include Count = COUNTROWS ( FILTER ( ALL ( TableB ), SEARCH ( TableB[CODE B: PARENT], TableA[code b concatenated], 1, -1 ) > 0 ) ) + 0BTW, it seems like no suitable record in your sample data.
Regards,
Xiaoxin Sheng
9 Replies
- Greg_Deckler
Community Champion
I would think that the IN operator would be your friend her. But, sample data would be great to test this theory. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
i couldn't upload the powerbi file.
1st screenshot is table A
2nd table B -> here i need a formula to count the "code B:parent" if are included in any of the table A "code B concatenated"; however the search of "code B:parent" needs to have attached a comma "," to avoid mistakes.
3rd is a simple overview of the 2 tables, no relationship are possible so far (unless there's a method to work on the "code B: concatenated")
