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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Bobiverse
Frequent Visitor

New column with count conditional for customer ID

Hi,

I have a table with columns

  • Customer_ID
  • Store_Brand
  • Order count
  • Date

The Store_Brand has different store brands

I want to have a column that counts for each Customer_ID row the distinct Store_Brand count.

Currently I keep getting the distinct count for the full table and not for the specific Customer_ID on the row

What DAX would solve this issue?

1 ACCEPTED SOLUTION

Hi @Bobiverse ,

 

Please try:

Distinct_Store_Brand_Count = CALCULATE(DISTINCTCOUNT('Table'[Store_Brand]),FILTER('Table',[Customer_ID]=EARLIER('Table'[Customer_ID])))

Final output:

vjianbolimsft_0-1689584453768.png

Best Regards,

Jianbo Li

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

7 REPLIES 7
Bobiverse
Frequent Visitor

Hi Seeing if anyone could help solve this question.

I have a 'data view' table with columns

  • Customer_ID
  • Store_Brand
  • Order count
  • Date

The Store_Brand has different store brands

I want to have a column that counts for each Customer_ID row the distinct Store_Brand count.

Understand and expect if the same customer_id, that the count of distinct_store_brand_count will repeat, and that would be the expected behavior... but currently I am getting in 'data view' for this calculated column the same number for each row, which is incorrect.

What DAX would solve this issue?

Bobiverse_0-1689022510670.png

 

Thanks for sharing the data view, unfortunatey the picture is not clear, do you mind sharing the excel table data instead? (just copy and paste the table here)

Customer_IDStore_BrandOrder countDateDistinct_Store_Brand_Count
350-157d88f6-1b09-11LOBLINE147Friday, October 8, 2021??
350-157d88f6-1b09-11KINGFISHER139Sunday, December 19, 2021??
732-d39f19fd-e0f8-11KINGFISHER135Saturday, September 25, 2021??
350-157d88f6-1b09-11RANDWAY172Monday, November 8, 2021??
326-be30ce4a-6298-11LOBLINE55Friday, July 9, 2021??
275-0bbd3846-d6ad-11RANDWAY39Friday, September 24, 2021??
421-1835e540-4994-11NEW LION33Saturday, June 26, 2021??
305-9a1dff89-70e1-11RANDWAY54Saturday, January 8, 2022??
732-d39f19fd-e0f8-11RANDWAY96Friday, November 12, 2021??
1155-a4fa2166-40be-1RANDWAY84Friday, October 1, 2021??
326-be30ce4a-6298-11LOBLINE54Tuesday, December 21, 2021??
326-be30ce4a-6298-11LOBLINE128Friday, September 17, 2021??
350-157d88f6-1b09-11RANDWAY25Friday, November 19, 2021??
275-0bbd3846-d6ad-11KINGFISHER143Tuesday, September 21, 2021??
350-157d88f6-1b09-11LOBLINE121Saturday, November 27, 2021??
596-16f269c0-60e4-11LOBLINE116Saturday, August 28, 2021??
596-16f269c0-60e4-11NEW LION115Saturday, August 28, 2021??
467-3e2f8285-2304-11KINGFISHER155Wednesday, June 23, 2021??
677-9de425ff-43fe-11LOBLINE24Friday, October 1, 2021??
305-9a1dff89-70e1-11LOBLINE79Friday, October 29, 2021??
884-d1c80474-181a-11NEW LION54Saturday, June 19, 2021??
136-ee45a2dc-1e5f-11LOBLINE109Wednesday, December 29, 2021??
421-1835e540-4994-11RANDWAY151Thursday, October 14, 2021??
467-3e2f8285-2304-11NEW LION118Friday, September 10, 2021??
421-1835e540-4994-11KINGFISHER126Friday, December 3, 2021??
590-7fd21221-28a0-11RANDWAY93Wednesday, August 18, 2021??
732-d39f19fd-e0f8-11NEW LION42Thursday, December 23, 2021??
467-3e2f8285-2304-11LOBLINE40Thursday, January 13, 2022??

Hi @Bobiverse ,

 

Please try:

Distinct_Store_Brand_Count = CALCULATE(DISTINCTCOUNT('Table'[Store_Brand]),FILTER('Table',[Customer_ID]=EARLIER('Table'[Customer_ID])))

Final output:

vjianbolimsft_0-1689584453768.png

Best Regards,

Jianbo Li

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

Ashish_Mathur
Super User
Super User

Hi,

Drag Customer_ID to the Table and write this measure

Store brand count = distinctcount(Data[Store_Brand])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

Thanks for reply, but would need this in the 'data view' and not the reporting.

I understand and expect if the same customer_id, that the count of distinct_store_brand_count will repeat, and that would be the expected behavior... but currently I am getting in 'data view' for this calculated column the same number for each row, which is incorrect.

HamedM1125
Advocate I
Advocate I

Hello @Bobiverse 

Can you provide sample data, it will help!

Thanks,

Hamed 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

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.