+ Reply to Thread
Results 1 to 7 of 7

DAX Measure to rank facility ID by Customer and Date

  1. 03-13-2025, 07:11 PM #1
    Brawnystaff
    Brawnystaff is offline
    Forum Contributor
    Join Date
    09-04-2013
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    266

    DAX Measure to rank facility ID by Customer and Date

    I am looking for a PowerPivot measure to rank the date a customer went to a facility by date (e.g. the earliest date would be 1, next would be 2, etc).

    Attached is Excel sample workbook with data and expected results. Looking for two variations of results. Thanks.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-13-2025, 07:35 PM #2
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2506 Win 11
    Posts
    24,846

    Re: DAX Measure to rank facility ID by Customer and Date

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
    Reply With QuoteRegister To Reply
  3. 03-13-2025, 08:00 PM #3
    Brawnystaff
    Brawnystaff is offline
    Forum Contributor
    Join Date
    09-04-2013
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    266

    Re: DAX Measure to rank facility ID by Customer and Date

    Not sure how to implement that solution. Looking for a numerical result to rank the results..
    Reply With QuoteRegister To Reply
  4. 03-13-2025, 08:19 PM #4
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2506 Win 11
    Posts
    24,846

    Re: DAX Measure to rank facility ID by Customer and Date

    1. used power query to develop rank as shown in attached and below.

    Please Login or Register  to view this content.
    2. Close and Load to Data Model.
    3. In Power Pivot, created a pivot table
    4. Created Measure shown in previous post.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 03-13-2025, 08:48 PM #5
    Brawnystaff
    Brawnystaff is offline
    Forum Contributor
    Join Date
    09-04-2013
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    266

    Re: DAX Measure to rank facility ID by Customer and Date

    Excellent. A two-prong approach with Power Query and Power Pivot. Thanks.
    Reply With QuoteRegister To Reply
  6. 03-14-2025, 04:19 AM #6
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2506 Win 11
    Posts
    24,846

    Re: DAX Measure to rank facility ID by Customer and Date

    Thanks for the Rep
    Reply With QuoteRegister To Reply
  7. 03-14-2025, 05:55 AM #7
    romperstomper
    romperstomper is offline
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,045

    Re: DAX Measure to rank facility ID by Customer and Date

    You could add a calculated column to the data in PP:

    =RANKX(FILTER(Table1,Table1[CustomerName]=EARLIER(Table1[CustomerName])),Table1[Date],,ASC)
    Everyone who confuses correlation and causation ends up dead.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. rank each product per customer in date order
    By PGLeeM in forum Excel General
    Replies: 2
    Last Post: 12-24-2019, 07:06 AM
  2. [SOLVED] Excel array function that assigns employee code to customer based on rank series assigned
    By akhilraj005 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-21-2019, 02:28 PM
  3. [SOLVED] Formula (array?) to summarise and rank customer sales data
    By The_Snook in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-31-2016, 08:28 AM
  4. [SOLVED] A formula to find Customer based on rank
    By Aland2929 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-13-2016, 07:22 PM
  5. Power Pivot Measure to rank negetive and Positive numbers - Not working
    By rv02 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 12-04-2015, 11:31 AM
  6. [SOLVED] RANK.EQ Function in Power Pivot Measure
    By rv02 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-16-2015, 04:26 PM
  7. How to create search facility to find previous customer details from a sheet in excel?
    By ryan.andrews in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-02-2015, 07:53 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1