Forum Discussion
CountIF text contains ID
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
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))
3 Replies
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
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))
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 =...
