Not counting values, after distinctcount ordernumbers

[Anonymous]

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

[Anonymous]

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?

#@##$$%

[Ashish_Mathur · Accepted solution]

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]

Good morning,

Thanl you VERY much for thid TIP.

I sumplifyd the formaula and now it is working correct.

SUPER!!!

Gr Alfons

[Ashish_Mathur]

You are welcome.

[tex628]

Hi @Ashish_Mathur ,

I'm just posting since I'm also interested in why the two calculations are giving different outputs. 

The FILTER() statement has to interact with the USERELATIONSHIP condition in some way that i don't quite understand. 

If you manage to figure something out related to this, please let me know! 

CALCULATE(DISTINCTCOUNT('Table'[Column]),FILTER('Table','Table'[Condition]= "Ja"),USERELATIONSHIP('Calendar'[Datum],'Table'[Datum]))

CALCULATE(DISTINCTCOUNT('Table'[Column]),'Table'[Condition]="Ja",USERELATIONSHIP('Calendar'[Datum],'Table'[Datum]))

Br,
J

[Ashish_Mathur]

Hi,

I use a FILTER() function in 1 of the following situations:

1. Comparing a measure to a measure
2. Comparing a column to a column
3. Comparing a column to a measure

Since ours is a case of a simple filter (comparing a column to a fixed value), the FILTER() function is not required.