Counting true and false across columns

[LauraCawdronDav]

Hiya,

I want to make a new column which calculates the number of 'trues' for each opportunity across a number of other columns.

The example data is below, but for some context 'service' fields are not dropdowns, they're set up as individuals checkboxes (Service_A, Service_B, Service_C) so are therefore separate columns showing true or false. However I know need to count the number of services in each opportunity. Please can someone help me?

Laura 

OppName	Service_A	Service_B	Service_C	Count of Services
Name1		True		1
Name2	True	True		2
Name3		True	True	2
Name4	True	True	True	3

[loveyouball]

DAX is easy, CALCULATE makes DAX hard...

[mangaus1111]

Hi @LauraCawdronDav ,

try to substitute [Service] = TRUE() with [Service] = "TRUE"

Please hit the thumbs up & mark it as a solution if it helps you. Thanks.

[mangaus1111]

Hi @LauraCawdronDav ,

you can first unpivot your table in Power Query:

1) Original Table "Opportunity"

2) Unpivoted Table "Opportunity"

after that you can use this measure:

CountServices = COUNTROWS(Opportunity)

and get the below result:

Please hit the thumbs up & mark it as a solution if it helps you. Thanks.

[LauraCawdronDav]

Hiya, I don't think I can unpivot my table as this is data taken directly from our CRM system and there are 60 other columns and fields I'm using elsewhere (and with different measures). Is there a way to do this without messing up the other measures I have? 

Thanks!

Laura 

[Greg_Deckler · Accepted solution]

@LauraCawdronDav You could do this:

Measure =
  COUNTROWS(
    FILTER(
      UNION(
        SELECTCOLUMNS('Table',"Service",[Service_A]),
        SELECTCOLUMNS('Table',"Service",[Service_B]),
        SELECTCOLUMNS('Table',"Service",[Service_C])
      ),
     [Service] = TRUE()
   )
 )

[LauraCawdronDav]

Hi Greg,

I've tried the measure but that seems to just tell me that there are services, rather than count the number of services which is more what i'm looking for. Is that right?

Laura

[Greg_Deckler]

@LauraCawdronDav I have both the Measure and Column versions in the attached PBIX below signature. The Measure works as is, here is the column:

Column = 
    VAR __Opp = [OppName]
    VAR __Table = { [Service_A], [Service_B], [Service_C] }
RETURN
  COUNTROWS(
    FILTER(
     __Table,
     [Value] = TRUE()
   )
 )

[Greg_Deckler]

@LauraCawdronDav If you put that measure in a table visual along with Opportunity, it should return the count of the number of services in each opportunity.