Add new column and count the frequency of a boolean value

[Anonymous]

Hello Folks,

In the below table I have to add a new column and reference a sum of all True values in each row. 

The final output in that new column should be:

Kind of portfolio assets	Count
Bonds	4
Company Ceo	-
Investment Funds	3
Shares	3

I created a measure and used the following formula however it didn't work

COUNTAX(FILTER('Table',[Company1]=TRUE()),TRUE())

Please help.

Thanks and regards

[harshnathani · Accepted solution]

HI @Anonymous ,

I meant a Calculated Column in Power Bi Desktop.

Regards,

Harsh Nathani

[Anonymous · Accepted solution]

It worked Harsh, however, is there any way to make it dynamic?

Regards.

[harshnathani]

Hi @Anonymous ,

You can create a new Column.

Column =

var _company1 = IF ('Table'[Company1] = True(),1,0)

var _company2 = IF ('Table'[Company2] = True(),1,0)

var _company3 = IF('Table'[Company3] = True(),1,0)

var _company4 = IF ('Table'[Company4] = True(),1,0)

var _suum = _company1 +_company2 + _company3 + _company4 

Return

_suum

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous · Accepted solution]

It worked Harsh, however, is there any way to make it dynamic?

Regards.

[harshnathani · Accepted solution]

HI @Anonymous ,

You can unpivot.

Select 1 column and click on Unpivot Columns --> Select Unpivot Other Columns

Your Table will change to the below.

Post this, you can apply filter.

Apply filter First and then change value to Count.

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Working as expected. Thanks Harsh.

A last question if I may or I will open a new discussion, with this last approach I want to show the entire set of table with this new dynamic way of getting the value count - how to do that ? do I have to merge or some other magic ?

Thanks in advance.

[Anonymous]

Thanks for your quick reply Harsh.

You mean a Custom column ?

If so, getting this error

[harshnathani · Accepted solution]

HI @Anonymous ,

I meant a Calculated Column in Power Bi Desktop.

Regards,

Harsh Nathani