Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everyone, I am hoping someone can help me here.
In my rows of data I have a column called sessionID. This sessionID can be duplicated several times on different rows. However, when I use the sessionID as a distinct value it can give me the exact number of sessions.
Within each row I also have a value that would be something like "Backup Size" and these values can be different within each row. This means the same sessionID (showing in multiple rows) can show different values in "Backup Size"
What I would like to do is find the MAX Backup Size within a group of duplicate sessionIDs and then Average that over all unique sessionIDs.
sessionID 123456 Backup 0
sessionID 123456 Backup 2
sessionID 123456 Backup 1
This would return 1 session with a max backup of 2
Sorry this seems so complex. Please let me know if I can do anything to make it more clear.
Solved! Go to Solution.
hi @PBIn00b
Use this measure and Try it
MaxBackupSize-Measure = AVERAGEX(SUMMARIZE('Backup-Sessions';'Backup-Sessions'[SessionID];"MaxBackupSize";Max('Backup-Sessions'[Backup]));[MaxBackupSize])
Let me know is this help you
hi @PBIn00b
Use this measure and Try it
MaxBackupSize-Measure = AVERAGEX(SUMMARIZE('Backup-Sessions';'Backup-Sessions'[SessionID];"MaxBackupSize";Max('Backup-Sessions'[Backup]));[MaxBackupSize])
Let me know is this help you
Ok, I just checked some numbers and it appears accurate. I may want to change my filter from SessionID though because users will have multiple session IDs. I will find something more unique to each user.
Thanks a ton for your help!
I have to say, so far the PowerBI community has been pretty top notch. I have seen very few "hey, someone else asked that question so I am going to ignore you" posts.
Hi @Vvelarde,
I think that may have worked! The average is lower than I thought it would be, but I will check the numbers.
Can you give me a basic breakdown of what is happening in the formula?
The formula act in this way:
First Summarize the sessions and extract the Max Value in BackupSize Table.
Example:
Session 1 2
Session 1 3
Session 2 1
Session 2 4
The result of summarize is
Session 1 3
Session 2 4
Next with AverageX take the 2 results (3 y 4) and calculate the Average. In this example : 3.5
i had same issue... you explained very well! thank you!
I am very much a newbie to DAX and doing more complex PowerBI pieces. I appreciate your time on this.
Am newbie too; I discover DAX 2 months ago; before i never used. This forum is very good to read and learn all the expert's publications.
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |