Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Justas4478
Responsive Resident
Responsive Resident

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.

Justas4478_0-1713275797145.png

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.
Justas4478_1-1713275942567.png

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

1 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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

14 REPLIES 14
jgeddes
Super User
Super User

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 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





@jgeddes Thank you for explaning.

jgeddes
Super User
Super User

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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

Justas4478_0-1713276865601.png

 

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?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





@jgeddes Document number can have multiple SKU Numbers.

Justas4478_0-1713277696254.png

Is this what you are asking for?

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?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





@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

Justas4478_0-1713338086534.png 
This is date relationship:

Justas4478_1-1713338130313.png

Let me know if I can provide anything else.

 

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?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





@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:
Justas4478_0-1713423804593.png

After I select to see details I get this:

Justas4478_1-1713423864875.pngJustas4478_2-1713423913856.png

This is how data looks for 'Key' columns:

Justas4478_3-1713424035223.png

Thanks.

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?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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

Justas4478_0-1713445677665.png

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])

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.