cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
remaster Frequent Visitor
Frequent Visitor

Count when a group of columns of the same table have certain value

Hello guys, I'm really confused because I want to do this...

I have a table with this style

image.png

What I need is to Count how many times in a row the words "OutSite(MTY)" or "Rest Day" repeats.

Im looking to achieve this with the following formula

Absences = CALCULATE(
COUNTROWS('dbpenrh emergencypaymentroll'),
SEARCH("OutSite(MTY)",'dbpenrh emergencypaymentroll',1,0))

The problem is that the SEARCH function doesn't "search" in all the table but needs at least 1 column...

Absences = CALCULATE(
COUNTROWS('dbpenrh emergencypaymentroll'),
SEARCH("OutSite(MTY)",'dbpenrh emergencypaymentroll'[Day_1],1,0))

Thats different from what I need.

I need something like a range for the SEARCH that goes from Day_1 to Day_30... 

 

How can I do this? Is really giving me a bad time ....

Any help is so much appreciated

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Count when a group of columns of the same table have certain value

Hi,

In the Query Editor, select all columns other than the Day columns > Right click > Unpivot other columns.  Drag Value column to the row labels.  Write this measure

=COUNTROWS(Data)

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
amkumar5 Member
Member

Re: Count when a group of columns of the same table have certain value

remaster Frequent Visitor
Frequent Visitor

Re: Count when a group of columns of the same table have certain value

it's a different situation, what I'm trying to do is

count 

emergencypaymentroll [Day_1],emergencypaymentroll [Day_2],emergencypaymentroll [Day_3] ..... emergencypaymentroll [Day_30]

when = "OutSite(MTY)"

To get something like this, I think that I need One Measure per Search, but the first thing I need to solve is the search trought all the columns in the table.

OutSite(MTY) ||   3
Rest Day        ||   2
Abscence       ||   1

 

Super User
Super User

Re: Count when a group of columns of the same table have certain value

Hi,

In the Query Editor, select all columns other than the Day columns > Right click > Unpivot other columns.  Drag Value column to the row labels.  Write this measure

=COUNTROWS(Data)

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 51 members 937 guests
Please welcome our newest community members: