Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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):
Why is this happening? Is it documented somewhere?
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.
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |