cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

Matrix, some rows are selected:

image.png

 

1) DayCount = DISTINCTCOUNT('time'[DATE].[Day])

Gives the number of days selected, here - 2.

image.png

 

2) HoursCount = [DayCount] * 24

Self-explanatory;

image.png

 

Column from the initial table:

3) DURATION 

All values are taken from that column. With selection above I get

 image.png

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.

image.png

What goes wrong here?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

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

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!

View solution in original post

4 REPLIES 4
Highlighted
Advocate III
Advocate III

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

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

Highlighted
Frequent Visitor

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

Well, not sure what can I do with that, unfortulately; unless someone has another solution for counting days. Here's data sample:

 

3.13Tealeisure16-07-18 
0.049Self-caremaintenance16-07-18 
0.141Job - Idle, Commuting, Planning, ... (Maintenance)Job16-07-18  
0.415Eating, cooking, shopping, kitchenmaintenance16-07-18 

 

It's from a time tracker.

Pretty please?

Highlighted
Advocate III
Advocate III

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

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/
Highlighted
Frequent Visitor

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

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!

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors