Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
Hi,
I have a table with columns
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?
Solved! Go to 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.
Hi Seeing if anyone could help solve this question.
I have a 'data view' table with columns
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?
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_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 | ?? |
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.
Hi,
Drag Customer_ID to the Table and write this measure
Store brand count = distinctcount(Data[Store_Brand])
Hope this helps.
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
60 | |
57 |