Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DISTINCTCOUNT

  This Data is about the Monthly Serviced units. One unit can get serviced multiple times in a month or year. However, when I count the total unit serviced I do not want the same unit being accounte...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , Not very clear, if you are asking why total do not match, so distinct count is calculated again for total to that might not sum up.

 

to sum up you need

 

 Measure = sumx(Values(DATA[Month]), CALCULATE(DISTINCTCOUNT(DATA[SerialNumber])))

or


Measure = sumx(summarize(DATA, DATA[Month], DATA[Model],"_1", CALCULATE(DISTINCTCOUNT(DATA[SerialNumber]))),[_1])

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Here is the table and Desired Result for DISTINCT Count. 

 

 

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    5 years ago

    Anonymous 

    maybe you can create a column

    count = if(CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Month]<=EARLIER('Table'[Month])&&'Table'[Model]=EARLIER('Table'[Model])&&'Table'[serialnumber]=EARLIER('Table'[serialnumber])))>1,0,1)

    and put the new column in the value field

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you Ryan! Your recommended measure works great. However, if a unit(ServiceNumber) has been serviced twice in the same month, then the count shows ‘0’ instead of 1. Can it be fixed for it? Thanks again.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    Hi,

    Based on that data that you have shared, =distinctcount(data[serial number]) should return the correct result for every month.  If not, then share the link from where i can download your PBI file.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        5 years ago

        Hi,

        Try this measure

        =

        Count = if(HASONEVALUE(Data[Month]),DISTINCTCOUNT(Data[Model]),DISTINCTCOUNT(Data[SerialNumber]))
        Hope this helps.