Calculate with distinct as filter

[Anonymous]

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 using a distinct function in the calculate function, which results in the equation below. All the filters work, except for the distinct function, for a article it results in 13 orders in the last year for a certain customer, sold on 9 different dates. The function returns 13 instead of 9.

How can I solve this?

*I tried DISTINCTCOUNT, but for some reason my Power BI stops working when I use DISTINCTCOUNT

AantVerkArtDeb =

VAR

   debiteur = [Debiteur]

VAR

     artikelNummer = [ArtikelNummer]

return

    CALCULATE(

         COUNT(OregHis[Datum]);

         FILTER('OregHis';

             [ArtikelNummer] = artikelNummer);

         FILTER('OregHis';

             'OregHis'[Debiteur] = debiteur);

         FILTER('OregHis';

            'OregHis'[datum] > TODAY()-365);

         DISTINCT(OregHis[Datum]) )

[vanessafvg]

@Anonymous  can  you share some data?

[Anonymous]

@vanessafvg 

Here is the example I mentioned. 

The article is sold 13 times, but on 9 different days, where I need to get the 9. 

[vanessafvg]

please copy and paste the data not an image, @Anonymous  in order to solve the issue i need usable data to put in my own powerbi model to see.

[Anonymous]

@vanessafvg 

As the data is confidential, I can't just copy and share the data, i'm sorry and thank you.

[vanessafvg]

@Anonymous  well you just put it in an image, all im saying is take that data and copy and paste it in rather than providing in an image that way i can help you to solve your issue

[Anonymous]

ArtikelNummer	Datum	Debiteur	AantVerkArtDeb
18818	25-4-2018	950000	13
18818	13-5-2018	950000	13
18818	25-4-2018	950000	13
18818	3-5-2018	950000	13
18818	24-4-2018	950000	13
18818	25-4-2018	950000	13
18818	24-4-2018	950000	13
18818	23-4-2018	950000	13
18818	3-5-2018	950000	13
18818	20-4-2018	950000	13
18818	24-4-2018	950000	13
18818	25-4-2018	950000	13
18818	23-4-2018	950000	13

@vanessafvg 

[tex628]

AantVerkArtDeb =
VAR debiteur = RELATED(OrderHistorie[Debiteur])
VAR artikelNummer = [ArtikelNummer]
return
    CALCULATE(
         DISTINCTCOUNT(OregHis[Datum]);
         FILTER('OregHis';
             [ArtikelNummer] = artikelNummer);
         FILTER('OregHis';
             RELATED(OrderHistorie[Debiteur]) = debiteur);
         FILTER('OregHis';
            'OregHis'[datum] > TODAY()-365))

Try this:

[Anonymous]

@tex628  When I use the distinctcount function my power bi stops working..

[tex628]

It crashes? How big is your dataset?

[Anonymous]

@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]

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?

[vanessafvg]

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

[vanessafvg]

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

[vanessafvg]

@Anonymous  sorry it was difficult to replicate that data with only one table (only realised after), difficult for me to therefore test

have you tried the values function instead of distinct count

values https://docs.microsoft.com/en-us/dax/values-function-dax

https://www.youtube.com/watch?v=SX6h4zoZ_8I

[Anonymous]

@vanessafvg Yes, the values function didn't work either, unfortunately.

[vanessafvg]

@Anonymous  unfortunately its hard to contextualise what you doing without more data and by that i mean

what the data looks like in both tables you using, and the relationship between them.

[Anonymous]

@vanessafvg 

I only use 1 table (I did an extra calculated column with RELATED(orderhistorie[debiteur]) when I made te picture). 

I have updated the formula in my original post. 

[Anonymous]

@vanessafvg  like this:

ArtikelNummer	Datum	Debiteur	AantVerkArtDeb
18818	25-4-2018	950000	13
18818	13-5-2018	950000	13
18818	25-4-2018	950000	13
18818	3-5-2018	950000	13
18818	24-4-2018	950000	13
18818	25-4-2018	950000	13
18818	24-4-2018	950000	13
18818	23-4-2018	950000	13
18818	3-5-2018	950000	13
18818	20-4-2018	950000	13
18818	24-4-2018	950000	13
18818	25-4-2018	950000	13
18818	23-4-2018	950000	13

[v-lili6-msft · Accepted solution]

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]

@v-lili6-msft 

Thank you! for some reason the distinctcount function worked in this formula.

Cheers