Forum Discussion

Atseaukes's avatar
Atseaukes
Frequent Visitor
8 years ago
Solved

Index column based on date, multiple entries excluded based on time

Current colomns    Desired column
action iduser_iddatetime Index
2ac9756d-d46d-42ff-b759-6fb88beaf8f7111-06-1811:35:40 1
30ac92ed-8e81-4443-aed7-8261eb0a597c112-06-1820:15:18 2
8ab1ed3d-523f-4926-a4f5-a4c938e693da113-06-1816:55:40 3
a2119042-caf3-408d-8ed4-43f3ad197765212-06-1822:53:24 1
a8a95082-bc10-4d80-9333-41db49900df0212-06-1822:53:22 1
c3b7b029-4344-440f-a31c-8f54c84f1a73311-06-1815:43:11 1
666a64cf-cb1d-434a-93b5-ed49fe785218314-06-1815:43:11 2
5c3ae105-78a8-4adb-86ce-1ac670edb100315-06-1815:43:10 3
55f913f1-c14d-4824-9d27-a27bde8b93b5316-06-1815:43:09 4
cfcde4bf-cb48-46bc-a105-4f95fba50895407-06-1815:44:16 1
f1b312ae-1f09-4870-80ba-4475f7731679409-06-1822:53:22 2
5876a70a-eb01-4adf-9ff3-35fef3641148409-06-1822:53:20 2
      
The index is based on User_id per day    
If a User_id has multiple entries on the same day the index needs to be the same (see ID 2)

 

Above an example of my data. I rather new to powerbi and I cannot figure out how to exclude the multiple entries (like in ID 2) in my index.

 

The purpose is to see how active a user is in terms of the number of days since the first day of activity. 

 

Could anyone help me out how to resolve this?

  • Atseaukes's avatar
    Atseaukes
    8 years ago

    Step 1:

    Index =

    VAR UserFirstDate = MINX(FILTER('Events Completion';'Events Completion'[user_id] = EARLIER('Events Completion'[user_id]));'Events Completion'[DateTime])
    RETURN

    DIVIDE (DATEDIFF(UserFirstDate ;'Events Completion'[DateTime];DAY);1)+1

     

    Step 2:

    Rank =
    VAR d = 'Events Completion'[Index]
    VAR c = 'Events Completion'[user_id]
    RETURN
    CALCULATE (
    RANK.EQ ( d; 'Events Completion'[Index]; ASC );
    FILTER ( ALL ( 'Events Completion'); 'Events Completion'[user_id] = c ))

10 Replies

  • Atseaukes's avatar
    Atseaukes
    Frequent Visitor
    8 years ago
    Current colomns    Desired column
    action iduser_iddatetime Index
    2ac9756d-d46d-42ff-b759-6fb88beaf8f7111-06-1811:35:40 1
    30ac92ed-8e81-4443-aed7-8261eb0a597c112-06-1820:15:18 2
    8ab1ed3d-523f-4926-a4f5-a4c938e693da113-06-1816:55:40 3
    a2119042-caf3-408d-8ed4-43f3ad197765212-06-1822:53:24 1
    a8a95082-bc10-4d80-9333-41db49900df0212-06-1822:53:22 1
    c3b7b029-4344-440f-a31c-8f54c84f1a73311-06-1815:43:11 1
    666a64cf-cb1d-434a-93b5-ed49fe785218314-06-1815:43:11 2
    5c3ae105-78a8-4adb-86ce-1ac670edb100315-06-1815:43:10 3
    55f913f1-c14d-4824-9d27-a27bde8b93b5316-06-1815:43:09 4
    cfcde4bf-cb48-46bc-a105-4f95fba50895407-06-1815:44:16 1
    f1b312ae-1f09-4870-80ba-4475f7731679409-06-1822:53:22 2
    5876a70a-eb01-4adf-9ff3-35fef3641148409-06-1822:53:20 2
          
    The index is based on User_id per day    
    If a User_id has multiple entries on the same day the index needs to be the same (see ID 2)

     

    Above an example of my data. I rather new to powerbi and I cannot figure out how to exclude the multiple entries (like in ID 2) in my index.

     

    The purpose is to see how active a user is in terms of the number of days since the first day of activity. 

     

    Could anyone help me out how to resolve this?

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi  Atseaukes

     

    You could try this calculated column which takes into account date & time

     

    Column = 
VAR UserFirstDate = MINX(FILTER('Table1','Table1'[user_id] = EARLIER('Table1'[user_id])),'Table1'[date] & " " & 'Table1'[time])
RETURN DIVIDE(DATEDIFF(UserFirstDate ,'Table1'[date] & " " & 'Table1'[time],HOUR),24)+1

    Can adjusted as needed

    • Atseaukes's avatar
      Atseaukes
      Frequent Visitor
      8 years ago

      Hi Phil_Seamark,

       

      I used:

      Index =
      VAR UserFirstDate = MINX(FILTER('Events Completion';'Events Completion'[user_id] = EARLIER('Events Completion'[user_id]));'Events Completion'[date] & " " & 'Events Completion'[time])
      RETURN DIVIDE(DATEDIFF(UserFirstDate ;'Events Completion'[date] & " " & 'Events Completion'[time];HOUR);24)+1

       

      but somehow that didn't work. See the example hereunder.

       

       

      It gave back negative results and decimals which, in addition, are not the same for the same day... 

       

      Any clue?

      • Atseaukes's avatar
        Atseaukes
        Frequent Visitor
        8 years ago

        I made some changes:

        DayOfUse = VAR UserFirstDate = MINX(FILTER('Events Completion';'Events Completion'[user_id] = EARLIER('Events Completion'[user_id]));'Events Completion'[DateTime])
        RETURN DIVIDE(DATEDIFF(UserFirstDate ;'Events Completion'[DateTime];DAY);1)+1

         

        But this results in the day number after first use. So startdate gives value 1. Second date is af day 10 gives value 10.

        How to change the second (thirth, forth, etc) day into value 2?