cancel
Showing results for
Search instead 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())

Please help.

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 ?

Thanks in advance.

Helper III

Thanks for your quick reply Harsh.

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

## Helpful resources

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

#### Check it Out!

Click here to read more about the April 2021 Updates!

#### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors