Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ljx0648
Helper I
Helper I

Count distinct profile for distinct ID

Hi guys,

 

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

 

IDAssign toWeb ProfileProfile Attribute 1Profile Attribute 2More Attribute columns
1AA1YN
1AA2NY 
2BBYN 
3CCYY 
4DD1YN 
4ED1NN 

 

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:

IDAssign toWeb Profile# Distinct Web Profile
1AA12
1AA22
2BB1
3CC1
4DD11
4ED11

 

Any tips are appreciated. 

 

Thank you!

1 ACCEPTED SOLUTION
v-xiandat-msft
Community Support
Community Support

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:

vxiandatmsft_0-1708408897199.png

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:

vxiandatmsft_1-1708409078344.png

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.

 

View solution in original post

3 REPLIES 3
v-xiandat-msft
Community Support
Community Support

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:

vxiandatmsft_0-1708408897199.png

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:

vxiandatmsft_1-1708409078344.png

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
Super User
Super User

Is this what you are looking for?

Screenshot_1.png

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:

 

IDAssign toWeb ProfileProfile Attribute 1Profile Attribute 2More Attribute columns
1AA1YN...
1AA2NY 
2BBYN 
3CCYY 
4DD1YN 
4ED1NN

 

 

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.