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
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.
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
Solved! Go to 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.
Proud to be a 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
Proud to be a Super User! | |
Have you tried...
DISTINCTCOUNT('Outbound Delivery Document'[DocumentNumber])
Proud to be a Super User! | |
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?
Proud to be a Super User! | |
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?
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
This is date relationship:
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?
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.
Proud to be a Super User! | |
@jgeddes Morning,
I tried the measure it does not have any errors in the measure itself.
After I select to see details I get this:
This is how data looks for 'Key' columns:
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?
Proud to be a Super User! | |
@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:
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
108 | |
103 | |
93 | |
71 |
User | Count |
---|---|
173 | |
134 | |
132 | |
101 | |
95 |