cancel
Showing results for
Did you mean:
Helper III

## Add new column and count the frequency of a boolean value

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())

Thanks and regards

3 ACCEPTED SOLUTIONS
Super User III

HI @fmourtaza ,

I meant a Calculated Column in Power Bi Desktop.

Regards,

Harsh Nathani

Helper III

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

Regards.

Super User III

HI @fmourtaza ,

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)

6 REPLIES 6
Super User III

Hi @fmourtaza ,

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)

Helper III

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

Regards.

Super User III

HI @fmourtaza ,

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)

Helper III

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 ?

Helper III

You mean a Custom column ?

If so, getting this error

Super User III

HI @fmourtaza ,

I meant a Calculated Column in Power Bi Desktop.

Regards,

Harsh Nathani

Announcements