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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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 III
Advocate III

Hello @Bobiverse 

Can you provide sample data, it will help!

Thanks,

Hamed 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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