cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fmourtaza
Helper III
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. 

 

fmourtaza_0-1592907409047.png

 

The final output in that new column should be:

 

Kind of portfolio assetsCount
Bonds4
Company Ceo-
Investment Funds3
Shares3
  

 

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

HI @fmourtaza ,

 

I meant a Calculated Column in Power Bi Desktop.

1.jpg

 

 

Regards,

Harsh Nathani

 

View solution in original post

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

 

Regards.

View solution in original post

HI @fmourtaza ,

 

 

You can unpivot.

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

 

1.jpg

 

Your Table will change to the below.

 

2.JPG

 

 

Post this, you can apply filter.

 

Apply filter First and then change value to Count.

 

 

3.JPG

 

 

Regards,

Harsh Nathani

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

View solution in original post

6 REPLIES 6
harshnathani
Super User III
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)

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

 

Regards.

View solution in original post

HI @fmourtaza ,

 

 

You can unpivot.

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

 

1.jpg

 

Your Table will change to the below.

 

2.JPG

 

 

Post this, you can apply filter.

 

Apply filter First and then change value to Count.

 

 

3.JPG

 

 

Regards,

Harsh Nathani

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

View solution in original post

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.

Thanks for your quick reply Harsh.

You mean a Custom column ?

If so, getting this error

fmourtaza_0-1592908506648.png

 

HI @fmourtaza ,

 

I meant a Calculated Column in Power Bi Desktop.

1.jpg

 

 

Regards,

Harsh Nathani

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

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.

Top Kudoed Authors