Custom Column (Similar to countif in excel) across multiple columns

[jlys89]

Hi 

I'm trying to figure out how to the do the following in PowerQuery (M) for Power BI where I want to add 2 additional columns to my data set as seen below:

What i want to achieve is counting the amount of times that a given feature is procent in the columns Feature 1 - Feature 6 where features are grouped into categories. (The 2 custom columns i need - Category 1 and Category 2)

Category 1: Feature 1, Feature 3, Feature 5

Category 2: Feature 2, Feature 4, Feature 6

For ID 1 the prefered method would be a count of: 3 because Feature 1, Feature 3 and Feature 5 is present in the 6 columns.

Afterwards I want to devide this by the total number of features meaning we get:

Category 1: 3/3 = 1,00 or 100 %

Category 2: 0/3 = 0  or 0 %

ID	Feature 1	Feature 2	Feature 3	Feature 4	Feature 5	Feature 6	Features	Category 1	Category 2
1	Feature 1	null	Feature 3	null	Feature 5	null	3	1	0
2	null	null	Feature 3	null	Feature 5	Feature 6	3	0,666666667	0,33
3	null	null	Feature 3	null	Feature 5	Feature 6	3	0,666666667	0,33
4	null	Feature 2	null	Feature 4	null	null	2	0	0,67
5	null	null	null	Feature 4	null	null	1	0	0,33

Any help would be much appreciated 🙂

[v-henryk-mstf · Accepted solution]

Hi @jlys89 ,

According to your requirements, I created the corresponding data model according to the data you provided. I did the following test: first, create two columns of column1 and column2 according to the provided conditions, judge whether the rows that meet the requirements are set to 1, and the created Measure is used to count the number of different categories. Finally, the correct result is obtained.

Here is the sample pbix file.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@jlys89 , Try two new columns like

Category 1 = (if [Feature 1] <> null then 1 else 0 + if [Feature 2] <> null then 1 else 0 + if [Feature 3] <> null then 1 else 0 )/3

Category 2 = (if [Feature 2] <> null then 1 else 0 + if [Feature 4] <> null then 1 else 0 + if [Feature 6] <> null then 1 else 0 )/3