Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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. 

 

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 @Anonymous ,

 

I meant a Calculated Column in Power Bi Desktop.

1.jpg

 

 

Regards,

Harsh Nathani

 

View solution in original post

Anonymous
Not applicable

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

 

Regards.

View solution in original post

HI @Anonymous ,

 

 

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

HI @Anonymous ,

 

 

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)

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 ?

 

Thanks in advance.

Anonymous
Not applicable

Thanks for your quick reply Harsh.

You mean a Custom column ?

If so, getting this error

fmourtaza_0-1592908506648.png

 

HI @Anonymous ,

 

I meant a Calculated Column in Power Bi Desktop.

1.jpg

 

 

Regards,

Harsh Nathani

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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