Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Count different columns with distinctcount

Hi,

 

I have a problem with a Dax formula. I want to see in how many months a debtor has ordered a product. So i know the date when the debtor ordered his product and in which month it is. For example: If a debtor 365254 ordered in March, April, June and July, I want to see the number 4 in the table "Months ordered". I have a large table like this:

Debtor:     Month:           Months ordered:

365254     3 (March)        4

365254     4 (April)          4

365254     6 (June)          4

365254     7 (July)            4

637983     1 (January)      1

 

I tried this: Months ordered = CALCULATE(DISTINCTCOUNT(month);debtor) --> but didn't work out

 

Can anyone help me out with this, so I can see the results in my colomn "Months ordered"? Thankyou!

 

Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi Anonymous 

    You can use the following Expression

     

     

    Column = 
VAR M = YEAR(Table4[order date])
RETURN 
CALCULATE(
    DISTINCTCOUNT(Table4[Month]),
    FILTER(
        ALLEXCEPT(Table4, Table4[debtor]),
        YEAR(Table4[order date]) = M 
    )
)

     

     

    Regards,
    Mariusz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    Hi Anonymous ,

     

    You can try Calculated Column below.

     

    NewColumn = 
CALCULATE(
    COUNTROWS(yourTable),
    ALLEXCEPT(yourTable,yourTable[Debtor:])
)



    Regards,
    Mariusz

    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
      7 years ago

      Hi Mariusz,

       

      Thankyou for your reply, but it doesn't work out yet.

       

      With that formula I see how many times a debtor orderd in a year, for example 80 times, but I want to see in how many months the debtor has ordered. Maybe he ordered only in the first 7 months and in the last month. I would like to see the answer '8' in column 'months ordered'. Maybe you can help me out with this. Thankyou very much.

       

      Regards,

       

      Rick

      • Mariusz's avatar
        Mariusz
        Community Champion
        7 years ago

        Hi Anonymous 

        Can you provide a biger sample of data and a clear outcom, also do you need a Measure or Column?

        Regards,
        Mariusz

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.