Simple measure doesn't work as expected (while all constituents are correct)

[GrayStrider]

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?

[GrayStrider · Accepted solution]

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!

[mbdtz]

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 results

Hope that helps

[GrayStrider]

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?

[mbdtz]

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/