Forum Discussion

Justas4478's avatar
Justas4478
Icon for Post Prodigy rankPost Prodigy
2 years ago
Solved

Distinct count for each day

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

Need Help
  • jgeddes's avatar
    jgeddes
    2 years ago

    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.

14 Replies

  • jgeddes's avatar
    jgeddes
    Icon for Super User rankSuper User
    2 years ago

    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 

  • jgeddes's avatar
    jgeddes
    Icon for Super User rankSuper User
    2 years ago

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

      • jgeddes's avatar
        jgeddes
        Icon for Super User rankSuper User
        2 years ago

        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?