cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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
Community Champion

HI @Anonymous ,

I meant a Calculated Column in Power Bi Desktop.

Regards,

Harsh Nathani

Anonymous
Not applicable

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

Regards.

Community Champion

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)

6 REPLIES 6
Community Champion

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
Not applicable

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

Regards.

Community Champion

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
Not applicable

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 ?

Anonymous
Not applicable

You mean a Custom column ?

If so, getting this error

Community Champion

HI @Anonymous ,

I meant a Calculated Column in Power Bi Desktop.

Regards,

Harsh Nathani

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors