Forum Discussion
Simple measure doesn't work as expected (while all constituents are correct)
Matrix, some rows are selected:
1) DayCount = DISTINCTCOUNT('time'[DATE].[Day])
Gives the number of days selected, here - 2.
2) HoursCount = [DayCount] * 24
Self-explanatory;
Column from the initial table:
3) DURATION
All values are taken from that column. With selection above I get
48 hours for 2 days, and so on. All good so far. But:
Percent = 'time'[DURATION] (48) / [DayCount] (2)
Gives me 48 for some reason, and not 24.
What goes wrong here?
I figured it out, pretty stupid.. I just had to remove time from the column for values to be distinct. Simple type change didn't do the trick for some reason, I had to split column in parts. Thanks for your time everyone!
4 Replies
Hi GrayStrider,
The culprit may be the .[day] part, I threw some data together and when I include the .[day] it behaves strangely, but when you remove it and just do a distinctcount on the 'time'[DATE] I was able to get the expected resultsHope that helps
Well, not sure what can I do with that, unfortulately; unless someone has another solution for counting days. Here's data sample:
3.13 Tea leisure 16-07-18 0.049 Self-care maintenance 16-07-18 0.141 Job - Idle, Commuting, Planning, ... (Maintenance) Job 16-07-18 0.415 Eating, cooking, shopping, kitchen maintenance 16-07-18 It's from a time tracker.
Pretty please?
- Seeing the data you've linked, there should be no issue just removing the .day part of the formula BUT as a best practice, whenever I'm dealing with dates in Power BI I like to use a specific date table and a relationship
This is a great article: https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/
I figured it out, pretty stupid.. I just had to remove time from the column for values to be distinct. Simple type change didn't do the trick for some reason, I had to split column in parts. Thanks for your time everyone!
