topic Re: Ranking in DAX Commands and Tips

Ranking

Anonymous — Thu, 10 Feb 2022 20:53:42 GMT

Part	Date	time	Before	After	DESIRED RESULT
a	1/1/2022	9:00:00 AM		0.1	0
a	1/1/2022	9:15:00 AM	0.1	0.7	0
a	1/1/2022	11:00:00 AM	0.7	0.3	0
a	1/1/2022	3:00:00 PM	0.3	0.2	1
b	1/5/2022	8:00:00 AM		1	0
b	1/5/2022	11:30:00 AM	1	5	0
b	1/5/2022	4:00:00 PM	5	3	0
b	1/5/2022	11:00:00 PM	3	2	1

I have this file. and I would like to know how to acheive the desired resluts column. I have multiple parts that get changes made to the item file multiple times per day. I would like to know which change was made last according to Date and Time combination. Each part will need to have either a 0 or a 1 designator.

Re: Ranking

Greg_Deckler — Thu, 10 Feb 2022 21:11:07 GMT

Anonymous Maybe:

DESIRED RESULT column = VAR __Part = [Part] VAR __Date = [Date] VAR __time = [time] VAR __MaxDate = MAXX(FILTER('Table',[Part] = __Part),[Date]) VAR __MaxTime = MAXX(FILTER('Table',[Part] = __Part && [Date] = __MaxDate),[time]) RETURN IF([Part] = __Part && [Date] = __Date && [time] = __time,1,0)

Re: Ranking

Anonymous — Fri, 11 Feb 2022 13:48:02 GMT

Thanks Greg. This worked.

Re: Ranking

Swilson2112 — Mon, 14 Feb 2022 15:14:18 GMT

Part	Before Cost	After Cost	Date Time	Desired RandRank
A		0.415	2/8/19 11:09	0
A		0	2/8/19 11:09	0
A		0	2/8/19 11:09	0
A		24.1	2/8/19 11:09	0
A		0.065	2/8/19 11:09	0
A		103.5	2/8/19 11:09	0
A		103.5	2/8/19 11:09	0
A		46.5	2/8/19 11:09	0
A		36.5	2/8/19 11:09	0
A		36.5	2/8/19 11:09	0
A		36.5	2/8/19 11:09	0
A		36.5	2/8/19 11:09	0
A	36.5		2/8/19 11:12	1
A	0.415	0.49	2/13/19 6:27	0
A	103.5	76.5	2/13/19 6:29	0
A	103.5	76.5	2/13/19 6:29	1
A	0.065	0.0865	2/14/19 10:14	1
A	0.49	0.5195	2/27/19 12:19	1
A		46.5	4/18/19 8:41	1
A	0.5195	0.4495	6/14/19 15:17	1
A	76.5	103.5	2/20/20 16:03	0
A	76.5	103.5	2/20/20 16:03	1
A	24.1	38.98	6/5/20 14:20	1
A	0.4495	0.91	5/17/21 10:48	1
A	0.91	1.12	9/22/21 16:30	1
A	1.12	0.4495	12/3/21 7:28	0
A	0.4495	1.12	12/3/21 7:30	1
A	1.12	0.4495	12/10/21 6:53	0
A	0.4495	0.91	12/10/21 7:10	0
A	0.91	1.12	12/10/21 7:12	0
A	1.12	0.91	12/10/21 7:18	0
A	0.91	1.12	12/10/21 7:19	1
A	1.12	0.4395	1/26/22 16:09	0
A	0.4395	0.8455	1/26/22 16:42	0
A	0.8455	0.91	1/26/22 16:43	0
A	0.91	0.965	1/26/22 16:44	0
A	0.965	1.02	1/26/22 16:45	0
A	1.02	1.12	1/26/22 16:46	1

OK, How do I create this desired result? I have many parts and want the desired rank of "1" on the last combination of Part & DateTime for each specific day. (See table above with Desired Result column that I manually populated.)

Re: Ranking

Swilson2112 — Mon, 14 Feb 2022 16:01:30 GMT

Part	Before	After	Date Time	Desired RandRank
A		0.415	2/8/19 11:09	0
A		0	2/8/19 11:09	0
A		0	2/8/19 11:09	0
A		24.1	2/8/19 11:09	0
A		0.065	2/8/19 11:09	0
A		103.5	2/8/19 11:09	0
A		103.5	2/8/19 11:09	0
A		46.5	2/8/19 11:09	0
A		36.5	2/8/19 11:09	0
A		36.5	2/8/19 11:09	0
A		36.5	2/8/19 11:09	0
A		36.5	2/8/19 11:09	0
A	36.5		2/8/19 11:12	1
A	0.415	0.49	2/13/19 6:27	0
A	103.5	76.5	2/13/19 6:29	0
A	103.5	76.5	2/13/19 6:29	1
A	0.065	0.0865	2/14/19 10:14	1
A	0.49	0.5195	2/27/19 12:19	1
A		46.5	4/18/19 8:41	1
A	0.5195	0.4495	6/14/19 15:17	1
A	76.5	103.5	2/20/20 16:03	0
A	76.5	103.5	2/20/20 16:03	1
A	24.1	38.98	6/5/20 14:20	1
A	0.4495	0.91	5/17/21 10:48	1
A	0.91	1.12	9/22/21 16:30	1
A	1.12	0.4495	12/3/21 7:28	0
A	0.4495	1.12	12/3/21 7:30	1
A	1.12	0.4495	12/10/21 6:53	0
A	0.4495	0.91	12/10/21 7:10	0
A	0.91	1.12	12/10/21 7:12	0
A	1.12	0.91	12/10/21 7:18	0
A	0.91	1.12	12/10/21 7:19	1
A	1.12	0.4395	1/26/22 16:09	0
A	0.4395	0.8455	1/26/22 16:42	0
A	0.8455	0.91	1/26/22 16:43	0
A	0.91	0.965	1/26/22 16:44	0
A	0.965	1.02	1/26/22 16:45	0
A	1.02	1.12	1/26/22 16:46	1

OK, How do I create this desired result? I have many parts and want the desired rank of either "0" or "1". On the last combination of Part & DateTime for each specific day. (See table above with Desired Result column that I manually populated.). I have multiple changes happening each day, but I only want to work with the last one of each day. I will then need to compare the ship date of another table to the closest after date that contains a "1" and then ruturn the text volue of the After field. (Text field type because it is also other changes) from the above table. I thought that I would need the 0's and 1's but if there is an easier way to do this I am more than willing to listen.

Re: Ranking

Swilson2112 — Mon, 14 Feb 2022 17:34:10 GMT

I figured this part out. Now I am trying to create a measure that compares the invoice date from table 1 with the closest lower Change date from table 2 and then returns the changed field from table 2.

Re: Ranking

Swilson2112 — Mon, 14 Feb 2022 17:51:54 GMT

EXAMPLE of tables and desired result.

TABLE 1

Transaction #. Part# Invoice Date.

1. A 02/14/2022

2. A 02/02/2022

TABLE 2

Part# Chnage Date. Change

A. 02/10/2022. 1.25

A 02/05/2022 0.37

A 01/15/2022 2.56

Desired Resultes

Transaction Return Result. Notes:

1 1.25 Chnage date of 02/10/2022 is closest less than date from invoice date of 02/14/2022

2 2.56 Chnage date of 01/15/2022 is closest less than date from invoice date of 02/02/2022