Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I want to use DAX to help me find the average distance ran in a game of football which can be compared to training sessions.
All of my games are assigned with a "game" tag but I can't get the DAX right
SUM('Data Dump 2021'[TD]), IF= 'Data Dump 2021'[Session type]= Game
Any help would be great
Hi !
You can use following DAX measure to get desired output;
Avg. Run Time = CALCULATE(AVERAGE('Data Dump 2021'[TD]), FILTER('Data Dump 2021', 'Data Dump 2021'[Sessin Type] = "Game"))
Regards,
Hasham
Hi Jihwan,
Thats been really helpful thank you, I just need an average now instead of SUM and the SUM is adding all the running up
Hi, @Calumc12
Thank you for your feedback.
please try the below for finding the average.
new measure =
CALCULATE (
AVERAGE ( 'Data Dump 2021'[TD] ),
'Data Dump 2021'[session type] = "Game"
)
Or, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi Jihwan,
This is brilliant and has done exactly what I am looking for. The issue i am running into now..
So now I have the distance I run on a game day, I want to compare it to what i ran in training (for example) - I have this data to hand and I have worked out a percentage change between the two variables (game distance / training distance-1)
The issue I have now is when i chnage the date to any session which isnt a game I get no data returned and therefore the % chnage is "infinty"
Any ideas how I can stop the game distance to not be responsive to the date selection?
Hi !
You can create 3 measures like below;
Avg. Game Run Time = CALCULATE(AVERAGE('Data Dump 2021'[TD]), FILTER('Data Dump 2021', 'Data Dump 2021'[Sessin Type] = "Game"))
Avg. Session Run Time = CALCULATE(AVERAGE('Data Dump 2021'[TD]), FILTER('Data Dump 2021', 'Data Dump 2021'[Sessin Type] <> "Game"))
% Diff Run Time = DIVIDE(Avg. Game Run Time, Avg. Session Run Time) - 1
The first measure will get you Avg. Game Run time, while second measure will calculate Avg. Run time for all other session type, you can change the <> "Game" to whatever session type you wan to compare.
Third measure is used to calculate % Difference between Game run time vs training run time.
Regards,
Hasham
HI Hasham,
Thanks for your reply, this is great but the problem im running into is that I can get "game run time" unless my session is on that date.
Usually I would want to compare the game norms to a training session although these are on different days and therefore I can compare the two
Hi !
So basically you want to compare Game Run time with Training Run time irrespective of dates.
Or do you want to compare these 2 KPI's monthly, yearly. For each month what's the Avg. Run time for Game vs. Training session.
Regards,
Hasham
Hi, @Calumc12
Thank you for your feedback.
What I can suggest is that try using CALCULATE function and ALL function.
If it is OK with you, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.
Thanks.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi Jihwan, sorry how do I send the file into here?
Hi, @Calumc12
Please try the below for SUM measure.
new measure =
CALCULATE (
SUM ( 'Data Dump 2021'[TD] ),
'Data Dump 2021'[session type] = "Game"
)
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
69 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
126 | |
32 | |
28 | |
24 | |
24 |