Total number of services used by a customer.

[Div-y-ansh_M]

Hey there, I came across a problem which I'm not sure how to solve.

I've 4 Columns i.e., Internet, Phone, TV & CustomerID. In first 3 columns, if a user has opted for it then it's a "yes" else it says "no".

Now, I want to count the total number of services a user has opted for.

I'm new to BI & I thought that maybe I'll get some help here.

[daXtreme]

@Div-y-ansh_M 

This data model is not what PBI likes. Unpivot the 3 columns, so that your table looks like this:

CustomerID | Service  | Value

--------------------------------

1                  | TV          | Yes

1                  | Phone    | No

1                  | Internet | Yes

2                  | TV          | Yes

2                  | Phone    | No

....

Once you've got it, then it's dead easy (if you put the CustomerID on rows in a table/matrix visual):

[# Services Opted For] =
calculate(
    // You have to decide how you want to
    // calculate this statistic when many
    // CustomerID's are visible in the
    // current context. Here I calculate
    // the number of different services
    // that at least one customer in the
    // current context has opted for.
    distinctcount( T[Service] ),
    keepfilters( T[Value] = "yes" )
)

[ValtteriN · Accepted solution]

Hi,

Here is one way to do this:

Data:

Dax:

Opted services =

Var C1 = if(MAX(OptedServices[C1])="Yes",1,0)

var c2 = if(MAX(OptedServices[C2])="Yes",1,0)

var c3 = if(MAX(OptedServices[C3])="Yes",1,0)

Return

c1+c2+c3

End result:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/