Forum Discussion

randu's avatar
randu
Frequent Visitor
2 years ago

Creating a visualization with frequencies as categories

Hello everyone!

Let me explain my question with an example.

I have the following data:

DayUserIdSessions
01/01/2024ABC3
01/01/2024DEF2
01/01/2024GHI2
02/01/2024ABC5
02/01/2024DEF2
03/01/2024ABC1
03/01/2024DEF1
03/01/2024GHI3

Where "sessions" column is the number of times the given "userId" has used a platform on that "day".

What we want to achieve is a visualization where we will display the total amount of sessions and how many users has that amount of sessions. That is, following the example, if we want to display the data for the range 01/01/2024 to 03/01/2024 (both included) we will display:

SessionsAmount of users
91
52

What we are showing on this table is that we have 1 user (ABC) that has the total amount of 9 sessions during the period selected (01/01/2024 to 03/01/2024) and 2 users (DEF and GHI) that has the total amount of 5 sessions. If we would select another time range (for example 01/01/2024 to 02/01/2024, both included), we will get the following:

SessionsAmount of users
81
41
21

because we have 1 user that has 8 sessions (user ABC), 1 user with 4 (DEF) and 1 with 2 (GHI).

 

How could I create this kind of visualization taking into account that the values for "Sessions" column have to be dynamic and change acording to the date range selected.

 

Do not hesitate to ask if there is something not clear for this case.

Thank a lot in advance.

Albert

 

Need Help

3 Replies

  • vicky_'s avatar
    vicky_
    Icon for Super User rankSuper User
    2 years ago

    Hey!
    One way of doing this is to create a table that just lists out the number 1,2,... x (which can be done with the following DAX:

    sessions = GENERATESERIES(1, 100, 1))

    This will be the "sessions" column in your final table. Then I use the following to calculate the number of users.

    Amount of Users = 
var summaryTable = ADDCOLUMNS(SUMMARIZE('Table (2)', 'Table (2)'[UserId]), "sessions", CALCULATE(SUM('Table (2)'[Sessions])))
var toReturn = COUNTROWS(FILTER(summaryTable, [sessions] = SELECTEDVALUE(sessions[Value])))

return toReturn

     

    I hope this helps

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi randu ,

     

    You can  try formula like below:

    Table = 
VAR summarize_ =
    SUMMARIZE (
        FILTER (
        YourTableName,
        YourTableName[Day] <= MAX ( 'Date'[Date] )
            && YourTableName[Day] >= MIN ( 'Date'[Date] )
    ),
        YourTableName[UserId],
        "sumofsessions", SUM ( YourTableName[Sessions] )
    )
VAR result =
    ADDCOLUMNS (
        summarize_,
        "number of user",
            COUNTX (
                FILTER ( summarize_, [sumofsessions] = EARLIER ( [sumofsessions] ) ),
                [UserId]
            )
    )
RETURN
    SUMMARIZE ( result, [sumofsessions], [number of user] )

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • randu's avatar
      randu
      Frequent Visitor
      2 years ago

      Hi Adamk,

       

      Thanks for the solution provided. However, in this case it doesn't work for me. I think I didn't explained it properly.

      In our case, the user wants to filter by date, so following the example, he can decide to check for only 1 day, or for 2 of them or all of three, but in our data we will have always all. So in your formula, since you are filtering the day to be between max and min dates from Date table, is not being really dynamic if the user wants to filter for one, two days or three days.

       

      Thanks again for your answer. I really appreciate it.

       

      Best Regards,

      Albert