window function

2 Topics

How do I return the first value by order related to an ID as a window function?
What I am working with: I have the limitation that my data is sourced from a sharepoint folder and it appends tables from about 24 excel documents for a grand total of 17,000,000 records and about 40 columns. Half these columns are ID columns so they have a large number of distinct values, and they use text in the key values. I know this isn't how things should be done, but this is what I have to work with and I have to figure it out. This is why the solution has to be in DAX (preferably calculated columns) because there is no database to fold the query back on, and when I tried this in power query I had to cut off the report at about 45 minuts of refresh time. I need the attributes to all stay in the same relationship to each other that they started in, so MINX() and MAXX() will not work. LOOKUPVALUE() also does not work because it is not feasible to duplicate the ID column, that hurts performance too much and refresh time is already at 30 minutes. What I have: ID_COLUMN NAME_COLUMN REGION_COLUMN 1 Jon west 2 Bob west 2 Mary east 2 Aaron south 3 Tammy north 3 Bill east 4 Judith south 5 Ally west 5 Josh north What I would like DAX to do: (See the last two columns) ID_COLUMN NAME_COLUMN REGION_COLUMN My_New DAX_Measure My_New_DAX_Measure_column_2 1 Jon west Jon west 2 Bob west Bob west 2 Mary east Bob west 2 Aaron south Bob west 3 Tammy north Tammy north 3 Bill east Tammy north 4 Judith south Judith south 5 Ally west Ally west 5 Josh north Ally west I am open to any suggestion that can get this solution.
Solved
Hogan
6 years ago Place DAX Commands and Tips
Views
0likes
Comments
Seeking DAX Help for Unique Employee Headcount Over Time with Dynamic Status Filtering
Hello Power BI Enthusiasts! I trust you're all doing wonderfully. I'm in a bit of a bind with a DAX measure I'm trying to construct in Power BI, and I could really use your collective expertise. My goal is to create a measure that counts unique employee records based on their employee number and filters these counts by their employment status using a related status dimension. This measure is intended to be dynamic, reflecting changes over time, leveraging a unique timestamp that marks each status change for the employees. Despite my efforts, I'm encountering an issue with duplicates in the data, Here is the DAX I'm using Test_Headcount_Dimstatus = VAR __selecteddate = MAX(Dim_calendar_start[Date]) VAR LatestEmployeeRecord = SUMMARIZE( 'EMPLOYEES - Ceridian modified', 'EMPLOYEES - Ceridian modified'[EMPLOYEE_EMPLOYEEID], "LatestTimestamp", MAX('EMPLOYEES - Ceridian modified'[EMPLOYEEEMPLOYMENTSTATUS_CREATEDTIMESTAMP]) ) VAR FilteredEmployeeRecord = FILTER( LatestEmployeeRecord, [LatestTimestamp] <= __selecteddate ) RETURN COUNTROWS(FilteredEmployeeRecord) Accompanying this post is a snapshot of my model for reference. I've been grappling with this problem for over a month, and it's quite frustrating. Does anyone have any insights into what might be going wrong? Any suggestions or guidance would be greatly appreciated. Thank you for taking the time to help!
alexflo188
2 years ago Place DAX Commands and Tips
Views
0likes
Comments