Forum Discussion
How to use same data over 3 columns
I have a dataset very similar to below
| id | code 0 | code 0 time | code 1 | code 1 time | code 2 | code 2 time | last_time |
| 1 | AA | 1/21/2018 21:35 | CC | 1/20/2018 21:35 | 1/24/2018 21:35 | ||
| 2 | BB | 1/18/2018 21:35 | AA | 1/21/2018 21:35 | CC | 1/14/2018 21:35 | 1/24/2018 21:35 |
| 3 | CC | 1/15/2018 21:35 | 1/24/2018 21:35 | ||||
| 4 | DD | 1/14/2018 21:35 | AA | 1/18/2018 21:35 | BB | 1/21/2018 21:35 | 1/24/2018 21:35 |
And I want to get some averages and counts over each of these columns. Each 'code' column is all the same data just spread out over all 3. How can I get a count for example of how many times a code appears in all 3 columns in my data set. So I have a unique set of codes and a count of how many times they appear. From there I should be able to get the averages pretty easily.
| CODE | Count |
| AA | 3 |
| BB | 2 |
| CC | 3 |
| DD | 1 |
In addition to that I want to get some average times between each Code
So each code has a time I want to compare that time to 'last_time' and return a value ex. 4 days
and then get an average of time difference for each distinct code
I never really had to reiterate my DAX over multiple columns like this so any info would be very helpful!!
Thanks as always - great community here
Hi thmonte
What I would do, is to make each part of your data a seperate dataset. So you would have 3 tables one for code 0, code 1 and code 2
I would then append them all together, so that it formed one long table. You would be able to know which table belongs to which code, by adding in an extra column on each dataset with the CodeID.
That would then allow you to easily get your distinct count.
And if you ordered the data by the time, you would then also be able to do the time calculations between the columns.
4 Replies
Hi thmonte
What I would do, is to make each part of your data a seperate dataset. So you would have 3 tables one for code 0, code 1 and code 2
I would then append them all together, so that it formed one long table. You would be able to know which table belongs to which code, by adding in an extra column on each dataset with the CodeID.
That would then allow you to easily get your distinct count.
And if you ordered the data by the time, you would then also be able to do the time calculations between the columns.
Thanks for the response Ashish_Mathur - I am going to mark this closed as I did reshape my data to get a row for each "code"
I now move on to a new issue of referencing other rows based on conditions and using a value in that row for TIMEDIFF. This one is going to be tricky.
You are welcome.
