Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

How to find the maximum of a measured value from a SQL server

Hello,

I'm trying to find a way to display the best historical preformance for some company metrics. The data is from a SQL server and comes in two parts. A numerator value and a denominator value. However, there are multiple values per work day and in order to be accurate, the numerators and denominators from a specific work day must first be summed before being divided. 

 

Currently, I am able to display the data by creating a relationship to a table that is a list of individual work days so by creating a 1:Many relationship I can do the calculation using a measure (shown below)

 

AHS SHM CT = divide(sum(AHS_SHM[Cycle_Time_Numerator]),sum(AHS_SHM[Cycle_Time_Denominator]),0)
 
This works to be able to show the value im looking for (called SHM here) for each work day I am interested in. However, to make a good compairison I want to be able to extract the all time maximum value of SHM that willl be able to change when I refresh the data. 
 
The problem I'm running into is that all the functions that find a MAX value need a column of data to do so to. But since the calculation requires a sum I don't know how to use the formula from the measure, when related to a single work day to create a new table that I can find a maximum from. 
 
Is there an easier way to do this that I am missing? If not, I would love some help on how to write a solution.
 
Thanks!
1 ACCEPTED SOLUTION
Sahir_Maharaj
Super User
Super User

Hello @Anonymous,

 

1. Create a measure to calculate the numerator and denominator sum for each work day:

 

NumeratorSum = SUM(AHS_SHM[Cycle_Time_Numerator]) DenominatorSum = SUM(AHS_SHM[Cycle_Time_Denominator]) 

 

2. Create a measure to calculate the SHM value using the numerator and denominator sums:

 

AHS SHM CT = DIVIDE([NumeratorSum], [DenominatorSum], 0) 

 

3. Create a measure to find the all-time maximum SHM value:

 

MaxSHM = MAXX(ALL('AHS_SHM'[WorkDay]), [AHS SHM CT]) 

 

Let me know if you might need further assistance.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

View solution in original post

1 REPLY 1
Sahir_Maharaj
Super User
Super User

Hello @Anonymous,

 

1. Create a measure to calculate the numerator and denominator sum for each work day:

 

NumeratorSum = SUM(AHS_SHM[Cycle_Time_Numerator]) DenominatorSum = SUM(AHS_SHM[Cycle_Time_Denominator]) 

 

2. Create a measure to calculate the SHM value using the numerator and denominator sums:

 

AHS SHM CT = DIVIDE([NumeratorSum], [DenominatorSum], 0) 

 

3. Create a measure to find the all-time maximum SHM value:

 

MaxSHM = MAXX(ALL('AHS_SHM'[WorkDay]), [AHS SHM CT]) 

 

Let me know if you might need further assistance.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.