Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

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

1 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

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

View solution in original post

20 REPLIES 20
vanessafvg
Super User
Super User

@Anonymous  can  you share some data?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

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

 

Foto power bi distinct count.png

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.

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

@vanessafvg 

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

ArtikelNummerDatumDebiteurAantVerkArtDeb
1881825-4-201895000013
1881813-5-201895000013
1881825-4-201895000013
188183-5-201895000013
1881824-4-201895000013
1881825-4-201895000013
1881824-4-201895000013
1881823-4-201895000013
188183-5-201895000013
1881820-4-201895000013
1881824-4-201895000013
1881825-4-201895000013
1881823-4-201895000013

 

@vanessafvg 

tex628
Community Champion
Community Champion

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:


Connect on LinkedIn
Anonymous
Not applicable

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

tex628
Community Champion
Community Champion

It crashes? How big is your dataset?


Connect on LinkedIn
Anonymous
Not applicable

@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
Community Champion
Community Champion

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?


Connect on LinkedIn

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

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

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

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

@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
Not applicable

@vanessafvg  like this:

ArtikelNummerDatumDebiteurAantVerkArtDeb
1881825-4-201895000013
1881813-5-201895000013
1881825-4-201895000013
188183-5-201895000013
1881824-4-201895000013
1881825-4-201895000013
1881824-4-201895000013
1881823-4-201895000013
188183-5-201895000013
1881820-4-201895000013
1881824-4-201895000013
1881825-4-201895000013
1881823-4-201895000013

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-lili6-msft 

 

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

Cheers

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.