How to use same data over 3 columns

[thmonte]

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

[GilbertQ · Accepted solution]

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.

[Ashish_Mathur · Accepted solution]

Hi @thmonte,

I have solved this exact problem - Restructure the layout of datasets.  Look at Case 1.

[thmonte]

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.

[Ashish_Mathur]

You are welcome.

[GilbertQ · Accepted solution]

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.