Semi Additive measure problem with multiple variables

[Anonymous]

I am working on a report on the amount of sensitive information each user is putting into emails, SharePoint documents, etc.  Every time a user edits a document, a new scan is done on that document and the amount sensitive information is recorded.

Row	Date	User	Doc	Sensitive Type	Count
1	2/1	Russ	text1	ABA	2
2	2/10	Russ	text1	ABA	5
3	2/9	Fred	text1	ABA	3
4	2/12	Fred	text1	ABA	4
5	2/10	Russ	text1	SSN	6
6	2/2	Russ	text2	ABA	9
7	2/9	Tim	text3	SSN	7
8	2/15	Tim	text3	SSN	3

Assuming that I am reporting on the whole range of dates above, I want a set that is comprised of the bolded rows:

Row	Date	User	Doc	Sensitive Type	Count
1	2/1	Russ	text1	ABA	2
2	2/10	Russ	text1	ABA	5
3	2/9	Fred	text1	ABA	3
4	2/12	Fred	text1	ABA	4
5	2/10	Russ	text1	SSN	6
6	2/2	Russ	text2	ABA	9
7	2/9	Tim	text3	SSN	7
8	2/15	Tim	text3	SSN	3

By the way, these columns are in five different tables (date, user, document, sensitive, and fact).

Here are a couple scenarios:

Total by Sensitive

ABA:  18 (rows 2,4,6)

SSN:  9 (rows 5, 😎

Total:  27

Total by User

Russ:  20 (Rows 2, 5,6)

Fred:  4 (Row 4)

Tim: 3 (Row 😎

Total:  27

Same principal for document.

Date would be more complicated, and would depart from the above.  If I report one row for each date, still filtered for the whole period, I would have one row for each date that has data, but the total would remain 27.

It wouldn't be pretty, but I could do this with SQL using the Last_Value and Rank functions.

All of the examples of semi-additive measures have one variable (account name, product name).  What I have is the equivalent of the owner of accounts in a bank and the accounts.  Or the inventory of product in different locations.

My search engine skills have failed me this time.  I look forward to hearing what options I have.

[technolog · Accepted solution]

Alright, I get what you're trying to achieve. You want to get the latest value for each combination of User, Doc, and Sensitive Type, and then sum those up based on different scenarios. Let's break this down.

First, you'll need to create a measure that identifies the latest date for each combination of User, Doc, and Sensitive Type. You can use the MAXX function to do this.

Latest Date =
MAXX(
FILTER(
ALL('fact'),
'fact'[User] = EARLIER('fact'[User]) &&
'fact'[Doc] = EARLIER('fact'[Doc]) &&
'fact'[Sensitive Type] = EARLIER('fact'[Sensitive Type])
),
'fact'[Date]
)
Once you have the latest date, you can create a measure to get the Count for that latest date:

Latest Count =
CALCULATE(
SUM('fact'[Count]),
FILTER(
ALL('fact'),
'fact'[User] = EARLIER('fact'[User]) &&
'fact'[Doc] = EARLIER('fact'[Doc]) &&
'fact'[Sensitive Type] = EARLIER('fact'[Sensitive Type]) &&
'fact'[Date] = [Latest Date]
)
)
Now, you can use this Latest Count measure to sum up based on different scenarios:

Total by Sensitive: Just drag the Sensitive Type column to a table visual and then add the Latest Count measure. It will automatically sum up the latest counts for each sensitive type.
Total by User: Similarly, drag the User column to a table visual and then add the Latest Count measure. It will sum up the latest counts for each user.
Total by Document: You can do the same thing with the Doc column.
For the date scenario, you can drag the Date column to a table visual and then add the Latest Count measure. It will show the latest count for each date, but when you sum them up, it will still be 27 as you mentioned.