Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 ...
Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi Anonymous ,

     

    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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
HamedM1125
Advocate III
3 years ago

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)

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago
    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??
    • v-jianboli-msft's avatar
      v-jianboli-msft
      Community Support
      3 years ago

      Hi Anonymous ,

       

      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.