Distinct count for each day

[Justas4478]

Hi, I am trying to create measure to distinct count order document numbers for each day.
This is how data looks normaly.

This is the measure that I have tried so far.

Distinct count document numbers = COUNTROWS(DISTINCT('Outbound Delivery Document'[DocumentNumber]))
Unfortunatelly it does not work as I hoped.
This is result I get.

Unfortunatelly I get total in each row instead of distinct count of document numbers for each day.
How do I need to change the measure to make it work.

I dont know if it helps but distinct count numbers is text data type.
I cant do ususal distinct count sinc data models is using live connection.

Thanks

[jgeddes]

The TREATAS function allows you to build a relationship between tables that are not related. 
So in this case because you could not change the direction of the existing relationship to 'both' the TREATAS function essentially creates a relationship where the Outbound Delivery table filters the Outbound Delivery Document table.

Generally when I use TREATAS I also use REMOVEFILTERS so you have more control over which filters are actually being applied. Depending on your use context, you may be able to remove the 'REMOVEFILTERS' portion from the measure. 
DAX - TREATAS 

[Justas4478]

@jgeddes Thank you for explaning.

[jgeddes]

Have you tried...
DISTINCTCOUNT('Outbound Delivery Document'[DocumentNumber])

[Justas4478]

@jgeddes I did try it, I still get same results.

[jgeddes]

Is there any additional criteria you can add to the logic of the measure that would help to troubleshoot? I.e., are there any other columns in the table that can be used?

[Justas4478]

@jgeddes Document number can have multiple SKU Numbers.

Is this what you are asking for?

[jgeddes]

Yes. Just trying to get a picture of the model being used. 
Another question, the date column used in your visual is it the date column from the table that has the document numbers or is it from a different table?
If it is from a different table, how is it related to the document number table?

[Justas4478]

@jgeddes This is relations.
The date comes from this 'Date' table and 'Outbound Delivery' -  DocumentNumberKey is connected to 'Outbound Delivery Document' -  DocumentNumberKey  with relationship Many to 1

 
This is date relationship:

Let me know if I can provide anything else.

[jgeddes]

Thank you for the clarification. Can you change the cross-filter direction on the relationship to both and then check if the proper distinct counts are displayed?

[Justas4478]

@jgeddes  Hi, unfortunatelly I can't do any changes since data model is managed by 3rd party company so I dont have such control.

[jgeddes · Accepted solution]

Ok. So what I see is that the Outbound Delivery Document table does not have a date context in it. Since the Outbound Delivery Document table filters the Outbound Delivery table (one-way) the date context in the Outbound Delivery table (or as extension the Date table) cannot be applied to the Outbound Delivery Document as that would be filtering in the opposite direction of the current relationship filter direction.
So we can try a measure like...

Distinct Count Of Doc Number = 
CALCULATE(
    DISTINCTCOUNT(Outbound Delivery Document[DocumentNumber]),
    REMOVEFILTERS(Date[Date]),
    TREATAS(VALUES(Outbound Delivery[OutboundDeliveryDocumentKey]), Outbound Delivery Document[OutboundDeliveryDocumentKey])
)

Hopefully this works for you.

[Justas4478]

@jgeddes Morning,
I tried the measure it does not have any errors in the measure itself.

Distinct Count Of Doc Number =

CALCULATE(

    DISTINCTCOUNT('Outbound Delivery Document'[DocumentNumber]),

    REMOVEFILTERS(Date[Date]),

    TREATAS(VALUES('Outbound Delivery'[OutboundDeliveryDocumentKey]), 'Outbound Delivery Document'[OutboundDeliveryDocumentKey]))

But when it try to display data in the table visual I get this message:

After I select to see details I get this:

This is how data looks for 'Key' columns:

Thanks.

[jgeddes]

I will take at look at the details of this error this morning. In the mean time I do notice that every instance of a table and column in your screen shot has the format 'tablename'[column] except the first instance after the REMOVEFILTERS which is tablename[column]. Can you see if adding the single quotes around the tablename makes a difference at all?

[Justas4478]

@jgeddes It works, I dont know how I missed those quotes, I thought I checked all of them.

I think I understand majority of the DAX query.
Would it be possible to explain this part of DAX:

TREATAS(VALUES('Outbound Delivery'[OutboundDeliveryDocumentKey]), 'Outbound Delivery Document'[OutboundDeliveryDocumentKey])