Count distinct profile for distinct ID

[ljx0648]

Hi guys,

I am a table of data similar to the one below:

ID	Assign to	Web Profile	Profile Attribute 1	Profile Attribute 2	More Attribute columns
1	A	A1	Y	N	…
1	A	A2	N	Y
2	B	B	Y	N
3	C	C	Y	Y
4	D	D1	Y	N
4	E	D1	N	N

You will see for each customer ID, it can be assigned to different representatives - One ID can have several representatives to service at the same time like ID 4.

Meanwhile, eahc ID can have multiple web profile created for different purposes. 

My goal of the exercise here is to add one CONDITIONAL COLUMN in my powberbi datatable to display the amount of unique/distinct web profile each ID has.

End result should looks like:

ID	Assign to	Web Profile	# Distinct Web Profile
1	A	A1	2
1	A	A2	2
2	B	B	1
3	C	C	1
4	D	D1	1
4	E	D1	1

Any tips are appreciated. 

Thank you!

[Anonymous · Accepted solution]

Hi @ljx0648 ,

Ahmedx's solution is right , because you have multiple web Attributes, and you can use ALLEXCEPT to remove all other attribute column filters, and subsequently just filter the different rows of the assign to columns

Below is my table:

The following DAX might work for you:

Column_assign = 
   CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Assign to]))

The final output is shown in the following figure:

At this point in time, it would be difficult for you to compute this result with the rest of the variables in the property columns

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ahmedx]

Is this what you are looking for?

[ljx0648]

Thank you for you quick response and time!

My appology here, I think I have supplied a over simplied sampe here, the datatbale looks like the one below:

ID	Assign to	Web Profile	Profile Attribute 1	Profile Attribute 2	More Attribute columns
1	A	A1	Y	N	...
1	A	A2	N	Y
2	B	B	Y	N
3	C	C	Y	Y
4	D	D1	Y	N
4	E	D1	N	N

May I know if there is anyway to count distinct only using the variables from ID & Web profile columns?

Because there are many different attribute columns after web profile & every one contains different info.

Thank you again for your time