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 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?

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.

7 Replies

  • HamedM1125's avatar
    HamedM1125
    Icon for Advocate III rankAdvocate III
    3 years ago

    Hello Anonymous 

    Can you provide sample data, it will help!

    Thanks,

    Hamed 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    Drag Customer_ID to the Table and write this measure

    Store brand count = distinctcount(Data[Store_Brand])

    Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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.

  • 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
      Icon for Advocate III rankAdvocate 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??