CODEWITHSUNDEEP
powerbidax
Hwende
Hwende

Reputation: 649

DAX - Count one row for each group that meet filter requirement

I have a UserLog table that logs the actions "ADDED", "DELETED" or "UPDATED" along with the date/timestamp.

In Power BI, I would like to accurately show the amount of new users as well as removed users for a filtered time period. Since it's possible that a user has been added and then deleted, I need to make sure that I only get the last record (ADDED/DELETED) from the log for every user.

Firstly, I tried setting up a measure that gets the max date/timestamp:

LastUpdate = CALCULATE(MAX(UserLog[LogDate]), UserLog[Action] <> "UPDATED")

I then tried to create the measure that shows the amount of new users:

AddedCount = CALCULATE(COUNT(UserLog[userId]), FILTER(UserLog, [Action] = "ADDED" && [LogDate] = [LastUpdate]))

But the result is not accurate as it still counts all "ADDED" records regardless if it's the last record or not.

Upvotes: 1

Views: 1101

Answers (1)

TheRizza
TheRizza

Reputation: 2052

I find writing formulas against unstructured data is much harder. If you put a little more structure to the data, it becomes much easier. There are lots of ways to do this. Here's one.

Add 2 columns to your UserLog table to help count adds and deletes:

Add = IF([Action]="ADDED",1,0)
Delete = IF([Action]="DELETED",1,0)

Then create a summary table so you can tell if the same user was added and deleted in the same [LogDate]:

Counts = SUMMARIZE('UserLog',
    [LogDate],
    [userId],
    "Add",SUM('UserLog'[Add]),
    "Delete",SUM('UserLog'[Delete]))

Then the measures are simple to write. For the AddedCount, just filter the rows where [Delete]=0, and for DeletedCount, just filter the rows were [Add]=0:

AddedCount = CALCULATE(SUM(Counts[Add]),
    Counts[Delete]=0,
    FILTER(Counts,Counts[LogDate]=[LastUpdate]))
DeletedCount = CALCULATE(SUM(Counts[Delete]),
    Counts[Add]=0,
    FILTER(Counts,Counts[LogDate]=[LastUpdate]))

Upvotes: 2

Related Questions