CountIF text contains ID

[dosinkevic]

Hello guys,

I'm new to PorweBI and writing Query formulas is super hard for me.

I'm trying to get same result as in GoogleSheets =COUNTIF(sales!L:L,"*"&CampaignID&"*")

I have two queries:

CAMPAIGN

id,     Campaign name,        spend
8745,   camp_1,               50
8456,   camp_2,               30
7763,   camp_3,               20

CRM

trans_id,  product,       camp_tracker
0012,      hat,           hat_8745_may
0013,      t-shirt,       t-shirt_8456_june
0014,      sneakers,      sneakers_7763_june
0015,      hat,           hat_8745_june

 I need to calculate if Campaign ID is in CRM column camp_tracker. I'm expecting result like that:

id,     Campaign name,        spend,  count_sales
8745,   camp_1,               50,     2
8456,   camp_2,               30,     1
7763,   camp_3,               20,     1

[Greg_Deckler · Accepted solution]

Create a column in your CAMPAIGN table like this:

Column = 
VAR __tmpTable = ADDCOLUMNS(ALL(crm),"Found",FIND([id],[camp_tracker],,-1))
RETURN COUNTROWS(FILTER(__tmpTable,[Found]<>-1))

[dosinkevic]

@Greg_Deckler, thank you very much! I was googling for hours..

Do you know is it possible to upgrade this function with IF statement?
If Campaign {"date"} = CRM {"date"} then Sales_count =...

[alexei7]

Hi @dosinkevic,

I presume that you have the campaign id field in your "CRM" table.

If you do, then you should be able to create a measure in CRM which is just "count_sales = COUNTROWS(CRM)

Then, in a table visualisation in Power BI, bring in all the fields from your Campaign table, and the new "count_sales" measure you've just created.

Hope that helps,

Alex