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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
MojoGene
Post Patron
Post Patron

Constant y-axis value based on field value?

I would like to show the high and low reference values as a line on a line chart along with the actual test value, but I struggling with how to approach the problem. The data table looks like this:

MojoGene_1-1698609679653.png

 

Thanks for any suggestions.

1 ACCEPTED SOLUTION

@MojoGene 
You need to work with both date and time dimensions hierarchically:

Ritaf1983_0-1699068094399.png

Ritaf1983_1-1699068311117.png

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

14 REPLIES 14
MojoGene
Post Patron
Post Patron

The intent is to show the lab value on a line chart with the high/low range of normal values like this:

MojoGene_0-1698755432089.png

 

MojoGene
Post Patron
Post Patron

Hi @MojoGene 
This is a data table, I still don't understand what you are trying to achieve....can you show an example?

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Hi and thanks for jumping in. The chart effect I am trying to achieve is shown on the image as mentioned above. The significance of any lab value must be assessed in relation to what is "normal," i.e., the range of expected values. I was thinking that this might be accomplished with dynamic constant lines that change with the lab test.

MojoGene_0-1698787231154.png

 

Hi @MojoGene 
There is no option to work with your sample data file because all data is about the same point in time + the values are not equal to the values on the pictures....

Ritaf1983_3-1698810245410.png

 


in general, you can add constant lines in this way :
After creating a line graph :

Ritaf1983_0-1698809356112.png

and then format it.
Maximum line in the same way.

These lines are dynamic:

Ritaf1983_1-1698809603849.pngRitaf1983_2-1698809654191.png

Pbix is attached.

More guides about working with constant lines :

https://www.easytweaks.com/add-horizontal-line-power-bi-chart-visual/

https://blogs.perficient.com/2023/05/03/analytics-pane-in-power-bi-part1/

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Thanks again for the reply. It occurred to me (duh!) as I was reading your reply that I uploaded data filtered for multiple tests at only one date/time. I should have filtered the data for a single test over multiple date/times as shown in the image. The constant lines I have in mind would only work with one lab test selected. With two or more selected the reference lines would clutter and confuse the chart.

 

So, I would need to display the high/low reference values with a measure that showed the lines when only one test was selected. 

Lab Data Sample 2 

Hi @MojoGene 
Now your sample has just one test ....if I understand correctly the table that you attached.

So I used again my file.

You can create customized constants. 
Ob the first step you need to create dimensions for date and lab tests with unique values.

Ritaf1983_0-1698899457807.png

These dimensions should be used as slicers and categories on the graph

Ritaf1983_1-1698899570434.pngRitaf1983_2-1698899626045.png

Then you can create measures for constants :

sum_for_Max = if(SELECTEDVALUE('location_T'[Location],0)=0,"",
CALCULATE(max(space_missions[Price]),ALLSELECTED('calendar'[Date])))
 
sum_for_min = if(SELECTEDVALUE('location_T'[Location],0)=0,"",
CALCULATE(min(space_missions[Price]),ALLSELECTED('calendar'[Date])))
 
Ritaf1983_3-1698899767578.png

Result :

Ritaf1983_4-1698899826394.pngRitaf1983_5-1698899863862.png

PBIX is attached

More information about dimension tables :

https://radacad.com/basics-of-modeling-in-power-bi-what-is-a-dimension-table-and-why-say-no-to-a-sin...

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Thanks again for your extraordinary effort. I can follow your explanation very well, but this will not work for my needs because the time dimension on the calendar (days) forces the lab results to be summarized for each day. The test results are most meaningful when shown as individual, non-aggregated values. The test results might provide some useful information if aggregated as AVE, MIN or MAX, but a SUM is meaningless for this context. 

 

Would I be able to get HOURS on the x-axis?

Hi @MojoGene 
You can work with every time granularity that you want, just create a time dimension table.
https://www.youtube.com/watch?v=-q7v56p192M&t=15s

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

I feel like I am very close here, but I can't seem to get Hours granularity on the Date hierarchy. 

@MojoGene 
You need to work with both date and time dimensions hierarchically:

Ritaf1983_0-1699068094399.png

Ritaf1983_1-1699068311117.png

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Thanks again, .@Ritaf1983 I have been able to get the hour dimension as you directed, although the results displayed at the hour granularity are not as expected. This is the display of average hourly value, which shows only 7 data points over the 20-day period of data. (Similar results for SUM, MAX, MIN.)

 

MojoGene_2-1699114433263.png

 

If a date with no data point is selected the graph is blank.

 

Based on your advice above, I did achieve the result I was looking for, but I achieved this using only the Date-Time field from the lab data table:

 

MojoGene_0-1699113959502.png

 

I am going to mark this as an accepted solution and once again extend my thanks for your extraordinary efforts. Above an beyond the call of duty.

 

Hi @MojoGene 
Glad to help.
According to the problems with the granularity, please post it as a new question, and attach a pbix with the model that you created with enough data (not one day, one lab, etc...) and expected results. 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile
Ritaf1983
Super User
Super User

Hi @MojoGene 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.