Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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!
Solved! Go to Solution.
Hi @Anonymous,
Please check out the demo in the attachment.
1. Select these two columns with "CTRL";
2. Pivot.
Best Regards,
Dale
@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!
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.
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:
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!
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.
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?
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
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!
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 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!
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |