Finding values in a column corresponding to MAX value of another column for each group

[swatcat]

Hi,

I have a table with the following columns:

UserId

SignInTimestamp

ClientVersion

Each time a user signs in, there is a new entry in the table.

I am trying to figure out what Client Version each user is on, on their latest SignInTimestamp.

I am relatively new to PowerBI and DAX, so I might be not thinking of some of the built in functions. My current approach is to summarize the table by UserId and return MAX(SignInTimestamp).

MaxDate = SUMMARIZE(signintelemetry,signintelemetry[UserId],"Max Date", MAX(signintelemetry[Timestamp]))

Then do an inner join of this table to the original table on UserId and timestamp. When I try to do a merge though, PowerBI doesn't allow me to merge on a derived table.

Any suggestions?

[Anonymous]

I would think something vaguely similiar to this:

=IF (HASONEVALUE(Telemetry[ClientVersion]),
    CALCULATE(
         VALUES(Telemetry[ClientVersion]),
         FILTER(Telemetry, Telemetry[SigninTimestamp] = MAX(Telemetry[SigninTimestamp]))
    )
)

[swatcat]

I tried that. It looks like the code returns the client version for the global MAX(timestamp). What I need is client version for MAX(timestamp) for each userid.

[Anonymous]

In retrospect, I don't think I like the IF (HASONEVALUE())

Recent Version :=

    CALCULATE(
         MIN(Telemetry[ClientVersion]),
         FILTER(Telemetry, Telemetry[SigninTimestamp] = MAX(Telemetry[SigninTimestamp]))
    )

But I would think if you use that as a Measure (as opposed to calc column) and you used (say) a Matrix visual w/ Users on the rows... it should work per user... No?!

[swatcat]

Thanks for the clarification. It looks like that works. But I needed to visualize this data in some form of chart. With the Matrix visual, I can only see it as a table. Is there a way to convert this matrix visualization to a chart?

[Anonymous]

Should work just the same for bar charts, etc... but maybe you are looking for something a bit different like... count of users on each version or something?

[swatcat]

Yes, I am looking for count of users on each client version.

[v-ljerr-msft]

Hi @swatcat,

Have you tried the solution provided by @Anonymous above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?

If you still have any question on this issue, feel free to post here. 

Regards

[Anonymous · Accepted solution]

I'm gonna be a bit lazy here, cuz it's friday... and your problem is making my head hurt 🙂

Create a "New Table" (from Modeling ribbon), use this expression:

VersionSummary = SUMMARIZE(Telemetry, Telemetry[UserId], "Version", [Recent Version]

This will give you an actual table of which version each user is on.  Then you can easily just use
 Total Count := COUNTROWS(VersionSummary)

and if you put "Version" on your visual axis, and [Total Count] in values... your life is complete.

There is almost certainly a "better" way to do this... and we can look for that... if you have a lot of unique users.