Custom Reference Line Using DAX

Hi mdm2025,

I would also take a moment to thank jaineshp , wardy912  for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Best Regards,
Harshitha.

Hi mdm2025 

 You will need to create a measure similar to the below, I am setting different targets for different days of the week.

Custom Reference Line = 
SWITCH(
    SELECTEDVALUE('Date'[Day Name]),
    "Monday", 1500,
    "Tuesday", 1300,
    "Wednesday", 500,
    "Thursday", 1200,
    "Friday", 900,
    "Saturday", 800,
    "Sunday", 700,
    BLANK()
)

Add that as the line y-axis in a line and clustered column chart for the following result

You could also add it as a tooltip for better visibility

I hope this helps! Please give a thumbs up and mark as solved if it does, thanks!

Hi ,

To create per-category reference lines, use a Line and clustered column chart plus a custom DAX measure. First, define a measure that returns the desired “target” for each category (e.g. day of week) using SWITCH and SELECTEDVALUE. For example, if your axis is the day of the week, you could write:

Custom Reference Line =
SWITCH(
SELECTEDVALUE('Date'[Day Name]),
"Monday", 1500,
"Tuesday", 1300,
"Wednesday", 500,
"Thursday", 1200,
"Friday", 900,
"Saturday", 800,
"Sunday", 700,
BLANK()
)

• Add the measure to the combo chart: In a Line and clustered column chart, put your primary measure in the Column values and the new Custom Reference Line measure in the Line valuescommunity.fabric.microsoft.com. Power BI will draw a horizontal line at the specified value for each column, effectively simulating a reference line per category.

• Optional – use tooltips: You can also include the custom measure in the tooltip to display exact values on hover.
  
  

   

  The result looks like this:
  
  
  
  
  
  Figure: A line-and-column chart where each day’s column is shown in blue and the custom reference line (target) in greencommunity.fabric.microsoft.comcommunity.fabric.microsoft.com.

  And with tooltips enabled, hovering over a column shows both the column value and the target. For example, hovering on Monday displays a tooltip with Order Quantity = 1884 and Custom Reference Line = 1500:
  
  
  
  
  
  Figure: Tooltip for Monday showing Order Quantity (1884) and the Custom Reference Line value (1500)community.fabric.microsoft.com.

  Steps summary:

  • Create a DAX measure using SWITCH(SELECTEDVALUE(...)) to output the target for each categorycommunity.fabric.microsoft.comcommunity.fabric.microsoft.com.

  • Use a Line and clustered column chart, placing the main measure in the column values and the custom measure in the line valuescommunity.fabric.microsoft.com.

  • (Optional) Add the measure to tooltips for clarity.

    This approach fully achieves per-column reference lines using DAX in Power BI.
    
    

     

  •  

  •  

  

  

•  

mdm2025

Hi mdm2025 can you please share pbix file so that I can understand what's going on

Hello everyone, 

Thank you for your prompt reply on this. 

I am not looking for the combo chart option as doesn't meet the visual requirements. The goal is to apply a individual line calculated on a custom measure for each of the columns. That is why I am using another column at the moment in lack of something similar. 
I will share a pbi example file once I put one together which will hopefully clarify the intent. 

Thank you,

Marius