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  When I use the distinctcount function my power bi stops working..

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

It crashes? How big is your dataset?

  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    7 years ago

    tex628 Anonymous  from the data provided  distinct count works but im not sure about the results, what error are you getting?  Is this a calculated column? (not a measure right?)

     

     

  • 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. 

  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Anonymous  how much memory do you have, ie. when you are running it can you watch the memory in your task manager under performance, also what are the data types of your fields?

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