Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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):

With absolute time diff of 2 hoursWith absolute time diff of 2 hoursWith absolute time diff of 1 hoursWith absolute time diff of 1 hours

Why is this happening? Is it documented somewhere?

3 REPLIES 3
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.