Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
Hi @thmonte,
I have solved this exact problem - Restructure the layout of datasets. Look at Case 1.
Hi @thmonte,
I have solved this exact problem - Restructure the layout of datasets. Look at Case 1.
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.
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.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |