Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate with distinct as filter

I am trying to count how many days a certain article has been sold to a certain customer in the last year. Because some customers buy some articles multiples times a day (in different orders), I am u...
Interesting Links
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Anonymous 

    You could try these two formulae:

    Column 3 = CALCULATE (
    DISTINCTCOUNT ( OregHis[Datum] ),
    FILTER (
        'OregHis',
        [ArtikelNummer] = EARLIER ( OregHis[ArtikelNummer] )
            && 'OregHis'[Debiteur] = EARLIER ( OregHis[Debiteur] )
            && 'OregHis'[datum] > TODAY () - 365
    )
)

    or

    Column 4 = CALCULATE (
    DISTINCTCOUNT ( OregHis[Datum] ),
    FILTER (
        ALLEXCEPT(OregHis,OregHis[ArtikelNummer],OregHis[Debiteur]), 'OregHis'[datum] > TODAY () - 365
    )
)

     

    Best Regards,
    Lin

Anonymous's avatar
Anonymous
Not applicable
7 years ago

tex628  101 mb's. The strange thing is that every other function works perfectly, but everytime I use distinctcount it gets stuck when it tries to calculate the formula. 

tex628's avatar
tex628
Icon for Community Champion rankCommunity Champion
7 years ago

Is the [Datum] column in dateTime format? Because that could cause the calculation to become much heavier.

Are you able to use distinctcount() normally on other columns?