Subtract values based on specific criteria

[Anonymous]

I'm in a pinch and attempting to get myself through a formula to figure out the differences of two columns, based on specific criteria. This is an audit table, and each item has it's own row every time the item is updated.

I'm looking to create two columns, to get two different values but it has to be based on the status. Here is the columns I'm using:

 https://imgur.com/a/qCNVdzA

What I would like is to be able to subtract:

1. BatchCount when: TrackingIds are the same, AuditReason=Other, RemovalTeamId=7 or 8, and UpdatedBy is = checkin for one value and audit by the second value.

2. UpdateDate when: TrackingIds are the same and UpdatedBy is checkout for one value and checkin for the other value.

Thank you!

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Please check out the demo in the attachment.

1. Select these two columns with "CTRL";

2. Pivot.

Best Regards,

Dale

[m_d]

I am having similar trouble as a new BI user. I'm trying to calculate session length that is dependent on the variables "action" and "user" or "ip address" to capture distinct sessions. I've tried a few different things but am not having much luck.

Based on this sample data (below) I'd expect that session length calc would result in the following results:

Expected Results:

Date	user	Session Length
1/1/2022	user1	60 mins
1/1/2022	user2	120  mins
1/2/2022	user1	60 mins
1/2/2022	user2	120 mins

Sample data

Username	Time	Action	IP Address	Date Created
user1	1/1/2022 9:15	Logout	123.45.678	1/1/2022
user1	1/1/2022 8:45	download	123.45.678	1/1/2022
user1	1/1/2022 8:25	download	123.45.678	1/1/2022
user1	1/1/2022 8:20	download	123.45.678	1/1/2022
user1	1/1/2022 8:15	Login	123.45.678	1/1/2022
user 2	1/1/2022 15:15	Logout	245.543.123	1/1/2022
user 2	1/1/2022 13:55	download	245.543.123	1/1/2022
user 2	1/1/2022 13:25	download	245.543.123	1/1/2022
user 2	1/1/2022 13:20	download	245.543.123	1/1/2022
user 2	1/1/2022 13:15	Login	245.543.123	1/1/2022
user1	1/2/2022 9:15	Logout	123.45.678	1/2/2022
user1	1/2/2022 8:45	download	123.45.678	1/2/2022
user1	1/2/2022 8:25	download	123.45.678	1/2/2022
user1	1/2/2022 8:20	download	123.45.678	1/2/2022
user1	1/2/2022 8:15	Login	123.45.678	1/2/2022
user 2	1/2/2022 15:15	Logout	245.543.123	1/2/2022
user 2	1/2/2022 13:55	download	245.543.123	1/2/2022
user 2	1/2/2022 13:25	download	245.543.123	1/2/2022
user 2	1/2/2022 13:20	download	245.543.123	1/2/2022
user 2	1/2/2022 13:15	Login	245.543.123	1/2/2022

need to isolate distinct users/sessions and then subtract time where action = logout vs login.

greatly appreciate any ideas.

[Anonymous]

I'm in a pinch and attempting to get myself through a formula to figure out the differences of two columns, based on specific criteria. This is an audit table, and each item has it's own row every time the item is updated.

I'm looking to create two columns, to get two different values but it has to be based on the status. Here is the columns I'm using:

 https://imgur.com/a/qCNVdzA

What I would like is to be able to subtract:

1. BatchCount when: TrackingIds are the same, AuditReason=Other, RemovalTeamId=7 or 8, and UpdatedBy is = checkin for one value and audit by the second value.

2. UpdateDate when: TrackingIds are the same and UpdatedBy is checkout for one value and checkin for the other value.

Thank you!

[Anonymous]

So you can only do datediff like this on columns.  So what you will likely need to do is pivot that data on the Updated by, so that you would have a column for checkin and Audit, the easy as that you can just datediff the checkin and audit columns

**Note, you may need to setup a dataset without those other columns that could be different as they as they would separate your rows.

[Anonymous]

I think I understand what you're saying, so basically I would need to filter down in to a new table to just the values that I need instead of attempting to filter it out in the larger table?

[Anonymous]

Kind of, what you really need is a wide table rather then a vertical one.

So instead of having::

Updatedate     updatedby
6/19 1:03     checkout
6/19 1:35     checkin

you would need 
checkout     checkin
6/19 1:03    6/19 1:35

[Anonymous]

Yeah, actually, that makes it really easy. I was able to run these sorts of stats in an older version of this database last year, and that's how it was formatted. This is normalized in sort of an 'audit' table but it doesn't make it easy for comparison of updates to each 'record'. Now I just have to figure out how to transpose the data like this. Don't think I'll get it in time for today like I was hoping, but at least this helps going forward. Thank you for the help!

[v-jiascu-msft]

Hi @Anonymous,

Is it something as follows?

Best Regards,

Dale

[Anonymous]

@v-jiascu-msft

Whoa!!! That's it!! And to go even further, it would be awesome to be able to string everything down the same row - like differing batch counts too. But this gets me much closer to what I was looking for.

How exactly do I go about doing this? I see the pivot, but I keep getting an error if I attempt to pivot based on either UpdateDate or UpdatedBy

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Please check out the demo in the attachment.

1. Select these two columns with "CTRL";

2. Pivot.

Best Regards,

Dale

[Anonymous · Accepted solution]

@v-jiascu-msft Thank you! It makes a lot more sense seeing what you did. Also, I think I was on the right track but the column had a lot of null values. That is what was causing the error. I duplicated the whole table, removed the columns I didn't need, filtered out null, then pivoted. Unfortunatley, I'm using DirectQuery and it's telling me that Pivot isn't compatible. So, at least this gets me a little closer because the data looks correct in that I know how I can manipulate it to start extracting what I need from it, like the times. I think I could do the same for BatchCount too. I'll just have to switch everything over to Import and get my gateway setup, and I should be on the right track. Thank you!