New column with count conditional for customer ID

[Bobiverse]

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?

[Bobiverse]

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?

[HamedM1125]

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)

[Bobiverse]

Customer_ID	Store_Brand	Order count	Date	Distinct_Store_Brand_Count
350-157d88f6-1b09-11	LOBLINE	147	Friday, October 8, 2021	??
350-157d88f6-1b09-11	KINGFISHER	139	Sunday, December 19, 2021	??
732-d39f19fd-e0f8-11	KINGFISHER	135	Saturday, September 25, 2021	??
350-157d88f6-1b09-11	RANDWAY	172	Monday, November 8, 2021	??
326-be30ce4a-6298-11	LOBLINE	55	Friday, July 9, 2021	??
275-0bbd3846-d6ad-11	RANDWAY	39	Friday, September 24, 2021	??
421-1835e540-4994-11	NEW LION	33	Saturday, June 26, 2021	??
305-9a1dff89-70e1-11	RANDWAY	54	Saturday, January 8, 2022	??
732-d39f19fd-e0f8-11	RANDWAY	96	Friday, November 12, 2021	??
1155-a4fa2166-40be-1	RANDWAY	84	Friday, October 1, 2021	??
326-be30ce4a-6298-11	LOBLINE	54	Tuesday, December 21, 2021	??
326-be30ce4a-6298-11	LOBLINE	128	Friday, September 17, 2021	??
350-157d88f6-1b09-11	RANDWAY	25	Friday, November 19, 2021	??
275-0bbd3846-d6ad-11	KINGFISHER	143	Tuesday, September 21, 2021	??
350-157d88f6-1b09-11	LOBLINE	121	Saturday, November 27, 2021	??
596-16f269c0-60e4-11	LOBLINE	116	Saturday, August 28, 2021	??
596-16f269c0-60e4-11	NEW LION	115	Saturday, August 28, 2021	??
467-3e2f8285-2304-11	KINGFISHER	155	Wednesday, June 23, 2021	??
677-9de425ff-43fe-11	LOBLINE	24	Friday, October 1, 2021	??
305-9a1dff89-70e1-11	LOBLINE	79	Friday, October 29, 2021	??
884-d1c80474-181a-11	NEW LION	54	Saturday, June 19, 2021	??
136-ee45a2dc-1e5f-11	LOBLINE	109	Wednesday, December 29, 2021	??
421-1835e540-4994-11	RANDWAY	151	Thursday, October 14, 2021	??
467-3e2f8285-2304-11	NEW LION	118	Friday, September 10, 2021	??
421-1835e540-4994-11	KINGFISHER	126	Friday, December 3, 2021	??
590-7fd21221-28a0-11	RANDWAY	93	Wednesday, August 18, 2021	??
732-d39f19fd-e0f8-11	NEW LION	42	Thursday, December 23, 2021	??
467-3e2f8285-2304-11	LOBLINE	40	Thursday, January 13, 2022	??

[v-jianboli-msft · 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:

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]

Hi,

Drag Customer_ID to the Table and write this measure

Store brand count = distinctcount(Data[Store_Brand])

Hope this helps.

[Bobiverse]

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]

Hello @Bobiverse 

Can you provide sample data, it will help!

Thanks,

Hamed