topic Re: Total number of services used by a customer. in DAX Commands and Tips

Total number of services used by a customer.

Div-y-ansh_M — Sat, 06 Aug 2022 18:31:34 GMT

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.

Re: Total number of services used by a customer.

ValtteriN — Sat, 06 Aug 2022 19:12:14 GMT

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/

Re: Total number of services used by a customer.

daXtreme — Sat, 06 Aug 2022 23:40:28 GMT

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" ) )