Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Not counting values, after distinctcount ordernumbers

Hello all,

 

I have allready posted the start of my problem on this forum.

For the biggest part my problem is solved, but the last piece is still missing.

 

I have a filter which is showing in a visual column chart the counts of  "loadings" and "unloadings".

 

The loadings are showing correct.

But the unloadings does not showing the correct counts.

 

First i need the filter on the distinct ordernumbers of the filtered date.

Then i need the counts "Ja" of the unloadings.

But the counts of unloading are not wright.

 

When i have a loading and unloading on 1 same day, The unloadings are not counted.

 

 

For this example, i miss 2 counts of unloading orders for the 26th.

 

 

 

Unieke telling Lading = CALCULATE(DISTINCTCOUNT(tblOrderOverzicht[Ordernr]),FILTER('tblOrderOverzicht','tblOrderOverzicht'[Lading]= "Ja"),USERELATIONSHIP('tblDatum'[Datum],tblOrderOverzicht[Laad Datum]))
 
Unieke telling Lossing = CALCULATE(DISTINCTCOUNT(tblOrderOverzicht[Ordernr]),FILTER('tblOrderOverzicht','tblOrderOverzicht'[Lossing]= "Ja"),USERELATIONSHIP('tblDatum'[Datum],tblOrderOverzicht[Los Datum]))
 
 
Do you guys have any idea for solving this?
 
My brains are exploding on this for 2 days.
 
Thanks,
 
Gr Alfons
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    I am not sure of why that is happening.  As a side note, you may simplify and shorten your formula to

    Unieke telling Lossing = CALCULATE(DISTINCTCOUNT(tblOrderOverzicht[Ordernr]),'tblOrderOverzicht'[Lossing]= "Ja",USERELATIONSHIP(tblOrderOverzicht[Los Datum],'tblDatum'[Datum]))

6 Replies

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

    Hi,

    I am not sure of why that is happening.  As a side note, you may simplify and shorten your formula to

    Unieke telling Lossing = CALCULATE(DISTINCTCOUNT(tblOrderOverzicht[Ordernr]),'tblOrderOverzicht'[Lossing]= "Ja",USERELATIONSHIP(tblOrderOverzicht[Los Datum],'tblDatum'[Datum]))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Good morning,

       

      Thanl you VERY much for thid TIP.

      I sumplifyd the formaula and now it is working correct.

       

      SUPER!!!

       

      Gr Alfons

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello all,

     

    Unfortunatly i was too fast happy with the solution.

    But sometimes the values still are not correct counted.

     

    If i am using the date filter, and select the whole week 22 my data column says 15 unloadings on 1 June, while tmy visual only displays 13 unloadings.

     

    15 orders/unloading actions are in the data sheet.

     

    Just 13 showed in the visual.

     

     

    What i do see, is that if i am only selecting June the 1st, so not a whole week, my visual says 6 unloading.

     

    I think this has to do with, that's the formula is also looking at the loading date.

    I have 5 blanks (These are only unloadings)

    and 1 loading on 1 June(the selcted date).

     

     

    I made the measure:

    Unieke telling Lossing = CALCULATE(DISTINCTCOUNT(tblOrderOverzicht[Ordernr]),tblOrderOverzicht[Lossing]="Ja",USERELATIONSHIP(tblDatum[Datum],tblOrderOverzicht[Los Datum]))
     
    So looking at:
    1th distinctcount of ordernumber
    2th Column Lossing all the "Yes" (Is 15 times in data column)
    3th Makes a relation with data column, Datum and Los Datum(Unloading date)
     

    What do i mis?

     

    #@##$$%