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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
ljx0648
Helper III
Helper III

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.