Find if True Value Exists for Each ID and Add 1

[gcv1999]

I have a table with data that looks like this:

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

[jstorm]

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

[amitchandak · Accepted solution]

@gcv1999 

Try Like

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