topic Re: Ranking based on date/time and 2 filters in DAX Commands and Tips

Ranking based on date/time and 2 filters

Anonymous — Mon, 13 Feb 2023 16:56:13 GMT

Hi,

Looking to create a ranking measure based on the earliest date/time to the latest date/time.

That is simple enough but need the rank to change / reset when both the Site/Plant and Sales Date change... So in effect will get the ranking by day by site/plant... The idea is I have another table with the Site/Plant & SAP CODE and the ranking order to compare the actual result to... Example if the result is 1,3,2,5,4 then it will show out of sequence or the like....

Attempts so far have been very limited....

Thanks in anticipation.

Sample data below...

Re: Ranking based on date/time and 2 filters

amitchandak — Tue, 14 Feb 2023 03:18:08 GMT

Anonymous , depending on what you include in rank

a new column=

rankx(filter(Table, [Site/ point] = earlier([Site/ point]) && [from_sap_code] =earlier([from_sap_code]) ), [Plan start],,asc,dense)

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

Re: Ranking based on date/time and 2 filters

Anonymous — Thu, 16 Feb 2023 09:14:26 GMT

Sorry for not getting back sooner. The way the data is setup I am unable to create the collumn in the dataset as it is direct query.

Also I may have got you all confused as the data is not all in a sequence either (Screenshot was a poor example) as this was already filtered and sorted to some extent.

So really looking for a measure that would Rank Items from 1 to xxx based on the Plan Start Date& Time going from Earlist to Latest where Sales Date, Site Plant are the same....