cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Find if True Value Exists for Each ID and Add 1

I have a table with data that looks like this:

Untitled.png

What I would like to do is for each ID, I want to see if there is a trainer attached to it, as shown by a true value in the isTrainer column, and then add 1 to a count. For example, for the ID ending in 7380, there exists a true value, so 1 should be added to the count. For the ID ending in 9910, there are multiple true values, but still just 1 should be added to the count. If there are only false values for an ID, then the count should not be incremented.

 

Thnks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: Find if True Value Exists for Each ID and Add 1

@gcv1999 

Try Like

countx(filter(summarize(Table, Table[ID], "_1",countx(filter(Table,Table[Istrainer]="Y"),Table[ID])),[_1]>=1),[ID])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
Highlighted
Super User IX
Super User IX

Re: Find if True Value Exists for Each ID and Add 1

@gcv1999 

Try Like

countx(filter(summarize(Table, Table[ID], "_1",countx(filter(Table,Table[Istrainer]="Y"),Table[ID])),[_1]>=1),[ID])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Post Patron
Post Patron

Re: Find if True Value Exists for Each ID and Add 1

This can be done in the 'Transform Data' window using a calculated column.  You will need Table.Distinct and Table.Contains in M Query.

If you do not want to work through the logic yourself, you can use 'Add Column from Example' to have Power BI determine the logic for you.

https://docs.microsoft.com/en-us/power-bi/desktop-add-column-from-example

 

 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors