Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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]) )
Solved! Go to 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 can you share some data?
Proud to be a Super User!
Here is the example I mentioned.
The article is sold 13 times, but on 9 different days, where I need to get the 9.
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.
Proud to be a Super User!
As the data is confidential, I can't just copy and share the data, i'm sorry and thank you.
@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
Proud to be a Super User!
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 |
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:
@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.
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?
@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?
Proud to be a Super User!
@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?)
Proud to be a Super User!
@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
Proud to be a Super User!
@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.
Proud to be a Super User!
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.
@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 |
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |