Forum Discussion

Fridm's avatar
Fridm
Frequent Visitor
10 years ago
Solved

DAX: Ranking of individuals

Hi everyone,

I have some troubles with PowerBI that I hope someone here can help me with. I have a list of People entering and exiting locations with timestamps that I am trying to convert to a format that would work with Sankey/Chord charts:

 

Current dataset

UserProfileIdAreaNameEnterUtcDate"EnterUtcTimeExitUtcDateExitUtcTime
963566X108.06.201610:43:0108.06.201610:43:32
963566X208.06.201610:44:2008.06.201610:44:54
961627X108.06.201610:50:1808.06.201610:52:31
961627X208.06.201611:09:1608.06.201611:09:16
961627X308.06.201611:10:5808.06.201611:12:28
961627X408.06.201611:45:2208.06.201611:46:43
961627X508.06.201612:03:0808.06.201612:04:01

 

The format I need to have the data in to do sankay/chord charts is

ToFromAmount
   

 

The solution I have come up with is to rank the each row based on individual and time of entry, an afterwards make a lookup on the individuals "next location".

UserProfileIdAreaNameEnterUtcDate"EnterUtcTimeExitUtcDateExitUtcTimeRankNext Area
963566X108.06.201610:43:0108.06.201610:43:321X2
963566X208.06.201610:44:2008.06.201610:44:542-
961627X108.06.201610:50:1808.06.201610:52:311X2
961627X208.06.201611:09:1608.06.201611:09:162X3
961627X308.06.201611:10:5808.06.201611:12:283X4
961627X408.06.201611:45:2208.06.201611:46:434X5
961627X508.06.201612:03:0808.06.201612:04:015-

 

Afterwards I imagine I could use some type of measure with a count formula to summarize the people entering and exiting a location.

 

My issue is that I cant seem to get the rank formula to work since I am not very profficient with PowerBI. I have been trying to use variations of RANKX: but seem to either rank the whole table, or each row individually. Can someone help me with how I can make a formula that only rank the results of each individual? Or can anyone come up with a better solution to my problem?

 

I might also ad that I have been making new columns converting the timestamps to digital formats, so I shouldnt have any issues with the date/time formats..

 

Thank you in advance!

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    10 years ago

    Hi Fridm,

     

    In your scenario, you can specify the RANKX( ) function to rank records with each [UserProfileId] instance. See:

     

    Rank = RANKX(
             FILTER(
             ALLSELECTED(Table1),Table1[UserProfileId]=EARLIER(Table1[UserProfileId])
                       ),
             'Table1'[EnterUtcTime],
         ,1)

     

     

    If you have any question, please feel free to ask.

     

    Best Regards,
    Qiuyun Yu

2 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    10 years ago

    Hi Fridm,

     

    In your scenario, you can specify the RANKX( ) function to rank records with each [UserProfileId] instance. See:

     

    Rank = RANKX(
             FILTER(
             ALLSELECTED(Table1),Table1[UserProfileId]=EARLIER(Table1[UserProfileId])
                       ),
             'Table1'[EnterUtcTime],
         ,1)

     

     

    If you have any question, please feel free to ask.

     

    Best Regards,
    Qiuyun Yu

    • Fridm's avatar
      Fridm
      Frequent Visitor
      10 years ago

      Thank you, this works great!

       

      Best regards,

      Michael