Forum Discussion
Create custom columns by counting specific rows in different table based on multiple filters
Hi all, I am quite new into powerbi and I am struggling with a project I am trying to build.
I have 2 tables:
Table1 - AllOpps
| opp-id | opp-name | opp-value |
| 1 | opp1 | value1 |
| 2 | opp2 | value2 |
| 3 | opp3 | value3 |
| 4 | opp4 | value4 |
Table2 - AllOppsWithContactRoles
| opp-id | opp-name | opp-value | ContactRole |
| 1 | opp1 | value1 | SalesContactRole1 |
| 1 | opp1 | value1 | TechnicalContactRole1 |
| 1 | opp1 | value1 | OtherContactRole1 |
| 2 | opp2 | value2 | TechnicalContactRole1 |
| 2 | opp2 | value2 | TechnicalContactRole2 |
| 3 | opp3 | value3 | SalesContactRole1 |
| 3 | opp3 | value3 | SalesContactRole2 |
| 3 | opp3 | value3 | SalesContactRole3 |
| 4 | opp4 | value4 | OtherContactRole1 |
The relationship between table1 and table2 is 1:many based on the opp-id field
What I am trying to achieve is the following - add 3 custom columns and count the info from table2 and represent it like this:
| opp-id | opp-name | opp-value | SalesContactRole | TechContactRole | OtherContactRole |
| 1 | opp1 | value1 | 1 | 1 | 1 |
| 2 | opp2 | value2 | 0 | 2 | 0 |
| 3 | opp3 | value3 | 3 | 0 | 0 |
| 4 | opp4 | value4 | 0 | 0 | 1 |
I have been testing with countrows and filter but then it counts for each item all rows he found (not filtering by opp-id) and I cannot figure it out.
Any suggestions?
Thank you,
DB
Hi, Anonymous
According to your description, I think you can use calculated columns in Power BI to achieve this requirement easily, you can follow my steps:
Create these calculated columns in the table ‘AllOpps’:
SalesContactRole = var _count= COUNTX( FILTER( RELATEDTABLE(AllOppsWithContactRoles),CONTAINSSTRING([ContactRole],"SalesContactRole")), 'AllOppsWithContactRoles'[ContactRole]) return IF(_count=BLANK(),0,_count)TechContactRole = var _count= COUNTX( FILTER( RELATEDTABLE(AllOppsWithContactRoles),CONTAINSSTRING([ContactRole],"TechnicalContactRole")), 'AllOppsWithContactRoles'[ContactRole]) return IF(_count=BLANK(),0,_count)OtherContactRole = var _count= COUNTX( FILTER( RELATEDTABLE(AllOppsWithContactRoles),CONTAINSSTRING([ContactRole],"OtherContactRole")), 'AllOppsWithContactRoles'[ContactRole]) return IF(_count=BLANK(),0,_count)Then you can get what you want, like this:
You can go to the report page and create a table chart like this:
You can download my test pbix file here
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi, Anonymous
According to your description, I think you can use calculated columns in Power BI to achieve this requirement easily, you can follow my steps:
Create these calculated columns in the table ‘AllOpps’:
SalesContactRole = var _count= COUNTX( FILTER( RELATEDTABLE(AllOppsWithContactRoles),CONTAINSSTRING([ContactRole],"SalesContactRole")), 'AllOppsWithContactRoles'[ContactRole]) return IF(_count=BLANK(),0,_count)TechContactRole = var _count= COUNTX( FILTER( RELATEDTABLE(AllOppsWithContactRoles),CONTAINSSTRING([ContactRole],"TechnicalContactRole")), 'AllOppsWithContactRoles'[ContactRole]) return IF(_count=BLANK(),0,_count)OtherContactRole = var _count= COUNTX( FILTER( RELATEDTABLE(AllOppsWithContactRoles),CONTAINSSTRING([ContactRole],"OtherContactRole")), 'AllOppsWithContactRoles'[ContactRole]) return IF(_count=BLANK(),0,_count)Then you can get what you want, like this:
You can go to the report page and create a table chart like this:
You can download my test pbix file here
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.