cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Why ADDCOLUMNS filters the table based on the expression of the new column?

I have a table with employees with their timezone information. I need to find that time zone interval (4 hours wide) which contains the most people. My idea was to create a table with all possible UTC offset values, crossjoin with my offsets and headcount, then check each interval for headcount and then select the maximum one. Here is my DAX for that:

HC TZ Overlap Reference = 
    VAR _OFFSET_HC = CROSSJOIN(
        GENERATESERIES(-12; 14; 0,5);
        SUMMARIZE(Employees;Employees[UTC Offset Hours];"HC";COUNT(Employees[mail_Base64]))
    )
    VAR _NEXT_STEP = ADDCOLUMNS(
        _OFFSET_HC;
        "Is within interval"; ABS([Value] - [UTC Offset Hours]) <= 2
    )
RETURN _NEXT_STEP

(I don't have any values nowhere near the -12 - 14 border, so I do not need to take care of that.)

 

My problem is that those lines where "Is within interval" is 'True' are not present. If I change the 2 to 1, then the previously excluded, but still 'False' values appear (Value, UTC Offset, Headcount, Is within interval):

image.pngWith absolute time diff of 2 hoursimage.pngWith absolute time diff of 1 hours

Why is this happening? Is it documented somewhere?

3 REPLIES 3
Super User
Super User

Re: Why ADDCOLUMNS filters the table based on the expression of the new column?

Very difficult to say with the information provided. I can't tell what columns are what. I guess what I am wondering is why you can't simply do this:

 

Measure = 
VAR __table = SUMMARIZE(Employees;Employees[UTC Offset Hours];"HC";COUNT(Employees[mail_Base64]))
VAR __max = MAXX(__table,[HC])
RETURN
MAXX(FILTER(__table,[HC] = __max),[UTC Offset Hours])

I believe that should return the time zone that has the maximum number of employees in it.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Anonymous
Not applicable

Re: Why ADDCOLUMNS filters the table based on the expression of the new column?

Hello, I put all the column name in order in parenthesis. I cannot do what you suggested because I don't need the time zone that has the most people in it, but the time interval (and I figured I could reference that with its middle point) which has a span of 4 hours and has the most employees.

Community Support Team
Community Support Team

Re: Why ADDCOLUMNS filters the table based on the expression of the new column?

@Anonymous,

 

You may take a look at the following post.

https://community.powerbi.com/t5/Desktop/Create-a-bar-graph-with-measures-as-axis/td-p/455900

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 380 members 3,243 guests
Please welcome our newest community members: