Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |