topic Re: Not able to use measure in the legend of a bar chart. Any alternatives ? in Report Server

Not able to use measure in the legend of a bar chart. Any alternatives ?

grkanth81 — Mon, 18 Aug 2025 08:57:42 GMT

Hi all,

I have created a DateTable to show last 7 days data as below:

DateTable = CALENDAR(TODAY()-7, TODAY()-1)

and there is a date slicer which shows last 7 dates. when the user selects any date, it should show a bar chart displaying count of employees per department and in the legend it should display how many employees target met and target not met. Here is the criteria for Target: If SUM of hours worked is greater than or equal to distinct dailytarget then Target reached else Target not reached. And when no date is selected, it should show how many employees have worked more than sum of distinct daily target?

EmployeeID	DateValue	Department	dailytarget	hoursworked
100	11/08/2025	HR	7.5	1
100	11/08/2025	HR	7.5	7
100	12/08/2025	HR	7.5	7
100	13/08/2025	HR	7.5	7
100	13/08/2025	HR	7.5	1.1
100	14/08/2025	HR	7.5	7
100	14/08/2025	HR	7.5	2.8
100	15/08/2025	HR	7.5	7
100	16/08/2025	HR	0	NULL
100	17/08/2025	HR	0	NULL
200	11/08/2025	Finance	6	0.3
200	11/08/2025	Finance	6	5
200	12/08/2025	Finance	6	5
200	12/08/2025	Finance	6	1
200	13/08/2025	Finance	6	1.5
200	13/08/2025	Finance	6	4
200	14/08/2025	Finance	6	2
200	14/08/2025	Finance	6	3
200	14/08/2025	Finance	6	1.5
200	15/08/2025	Finance	6	2.5
200	15/08/2025	Finance	6	1
200	15/08/2025	Finance	6	1.5
200	16/08/2025	Finance	0	NULL
200	17/08/2025	Finance	0	NULL
300	11/08/2025	HR	7.5	7.5
300	12/08/2025	HR	7.5	6
300	13/08/2025	HR	7.5	7.5
300	14/08/2025	HR	7.5	7.5
300	15/08/2025	HR	7.5	3
300	15/08/2025	HR	7.5	5
300	16/08/2025	HR	0	NULL
300	17/08/2025	HR	0	NULL
400	11/08/2025	Finance	7.5	1
400	11/08/2025	Finance	7.5	7
400	12/08/2025	Finance	7.5	7
400	13/08/2025	Finance	7.5	7
400	13/08/2025	Finance	7.5	1.1
400	14/08/2025	Finance	7.5	7
400	14/08/2025	Finance	7.5	2.8
400	15/08/2025	Finance	7.5	7.5
400	16/08/2025	Finance	0	NULL
400	17/08/2025	Finance	0	NULL

And I have created a calculated table as below to get the summarised data from the above:

Table = SUMMARIZE(EmployeeTable,EmployeeTable[EmployeeID],EmployeeTable[DailyTarget],EmployeeTable[Dept],EmployeeTable[DateValue],"SumOfHrs",SUM(EmployeeTable[hoursworked]))

from the above, I have created a new measure for the Target which goes - If sum of hours recorded is greater than or equal to sum of daily target the 'Target reached' else 'Target Not reached' so that I can use in the legend. But, I noticed that I cannot use measure in the legend.

Can someone provide any alternatives for this issue?

Thanks,

grkanth81

Re: Not able to use measure in the legend of a bar chart. Any alternatives ?

jaineshp — Mon, 18 Aug 2025 09:08:46 GMT

Hey grkanth81,

Here are some practical workarounds since Power BI doesn't allow measures in legend:

Solution 1: Calculated Column Approach

Add calculated column to your summarized table:

TargetStatus = IF([SumOfHrs] >= [DailyTarget], "Target Reached", "Target Not Reached")

Drag this column to Legend field in your bar chart
This gives you the exact functionality you're looking for

Solution 2: Matrix Visual Alternative

Create separate measures for target met/not met counts
Switch to matrix visual instead of bar chart
Shows department-wise breakdown with target status

Solution 3: Stacked Bar Configuration

Build calculated columns for each target status
Configure as stacked bar chart
Legend automatically displays target categories

Solution 4: Enhanced Tooltips

Keep standard bar chart showing department totals
Add target breakdown in tooltips using measures
Users see details on hover

Recommended Approach: Option 1 is your best bet - calculated columns work perfectly with legends and it's the cleanest solution for your requirement.

For handling the "no date selected" scenario, you'll need to add appropriate filter logic in your visual-level filters.

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer

Re: Not able to use measure in the legend of a bar chart. Any alternatives ?

grkanth81 — Mon, 18 Aug 2025 10:07:39 GMT

Thanks Jaineesh. Yes calculated column seems to be working when any date is selected but it is not working when no date is selected. Can you please explain what do you mean by the below?

For handling the "no date selected" scenario, you'll need to add appropriate filter logic in your visual-level filters.

Re: Not able to use measure in the legend of a bar chart. Any alternatives ?

jaineshp — Mon, 18 Aug 2025 10:16:16 GMT

Hey grkanth81,

When no date is selected in your slicer, the calculated column may not behave as expected because it's trying to evaluate against a blank or null date context.

Visual-Level Filter Solutions:

Option 1: Default Date Logic

Go to your visual's Filters pane
Add your Date field to visual-level filters
Set filter type to "Advanced filtering"
Configure condition: "is not blank" or "is not null"
This ensures the visual only shows data when dates are properly selected

Option 2: Modified Calculated Column Update your calculated column formula to handle blank dates:

TargetStatus =
IF(
ISBLANK([DateField]) || [SumOfHrs] = BLANK(),
"No Data Available",
IF([SumOfHrs] >= [DailyTarget], "Target Reached", "Target Not Reached")
)

Option 3: Visual Display Rules

In visual-level filters, add custom filter on your TargetStatus column
Exclude "No Data Available" or blank values
This prevents incomplete data from appearing in your legend

Option 4: Slicer Configuration

Configure your date slicer to have a default selection
Set it to show current month or current date by default
This eliminates the "no date selected" state entirely

Recommended Implementation: Combine Option 2 with Option 3 - modify the calculated column to handle blanks gracefully, then filter out incomplete records at the visual level.

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer

Re: Not able to use measure in the legend of a bar chart. Any alternatives ?

v-pnaroju-msft — Mon, 18 Aug 2025 15:37:41 GMT

Thankyou, jaineshp, for your response.

Hi grkanth81,

We appreciate your enquiry through the Microsoft Fabric Community Forum.

Based on my understanding, since measures cannot be placed in the Legend, a feasible workaround is to create a disconnected TargetStatus table and drive it with a measure that compares each employee’s total hours worked against the sum of distinct daily targets. Using this approach, the bar chart displays department counts split by target status. When a date is selected, it evaluates that specific day and when no date is selected, it evaluates the full 7 day window.

Please find attached the screenshots and a sample PBIX file, which may assist in resolving the issue:

We hope the information provided helps to resolve your concern. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

Re: Not able to use measure in the legend of a bar chart. Any alternatives ?

grkanth81 — Mon, 18 Aug 2025 23:00:10 GMT

Thank you v-pnaroju-msft. That worked. I have one more small question. How to sort it by departments showing with highest total count to lowest total count. I tried to change it here but it seems to be not working. Any ideas ?

Re: Not able to use measure in the legend of a bar chart. Any alternatives ?

v-pnaroju-msft — Tue, 19 Aug 2025 10:19:16 GMT

Hi grkanth81,

Thank you for your follow-up.

As per my understanding, because the legend is driven by a measure and a disconnected table, sorting directly on the legend will not affect the department order. Please find attached the revised sample PBIX file in which we have created a new measure to calculate the total employee count across all statuses. We have placed this measure in the tooltips field well of the visualizations pane and then sorted the visual by total employee count in descending order.

We hope the information provided will help resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

Re: Not able to use measure in the legend of a bar chart. Any alternatives ?

grkanth81 — Tue, 19 Aug 2025 11:59:08 GMT

Thanks again for your reply v-pnaroju-msft. yes that's working for the sample dummy data I provided. But in my actual dashboard which has many records it is not working. I have added some more sample records to get a better picture. I am not able to attach my pbix file. So here is the new sample data with more records. Any help is appreciated ?

EmployeeID	DateValue	Department	dailytarget	hoursworked
100	11/08/2025	HR	7.5	1
100	11/08/2025	HR	7.5	7
100	12/08/2025	HR	7.5	7
100	13/08/2025	HR	7.5	7
100	13/08/2025	HR	7.5	1.1
100	14/08/2025	HR	7.5	7
100	14/08/2025	HR	7.5	2.8
100	15/08/2025	HR	7.5	7
100	16/08/2025	HR	0
100	17/08/2025	HR	0
200	11/08/2025	Finance	6	0.3
200	11/08/2025	Finance	6	5
200	12/08/2025	Finance	6	5
200	12/08/2025	Finance	6	1
200	13/08/2025	Finance	6	1.5
200	13/08/2025	Finance	6	4
200	14/08/2025	Finance	6	2
200	14/08/2025	Finance	6	3
200	14/08/2025	Finance	6	1.5
200	15/08/2025	Finance	6	2.5
200	15/08/2025	Finance	6	1
200	15/08/2025	Finance	6	1.5
200	16/08/2025	Finance	0
200	17/08/2025	Finance	0
300	11/08/2025	HR	7.5	7.5
300	12/08/2025	HR	7.5	6
300	13/08/2025	HR	7.5	7.5
300	14/08/2025	HR	7.5	7.5
300	15/08/2025	HR	7.5	3
300	15/08/2025	HR	7.5	5
300	16/08/2025	HR	0
300	17/08/2025	HR	0
400	11/08/2025	Finance	7.5	1
400	11/08/2025	Finance	7.5	7
400	12/08/2025	Finance	7.5	7
400	13/08/2025	Finance	7.5	7
400	13/08/2025	Finance	7.5	1.1
400	14/08/2025	Finance	7.5	7
400	14/08/2025	Finance	7.5	2.8
400	15/08/2025	Finance	7.5	7.5
400	16/08/2025	Finance	0
400	17/08/2025	Finance	0
500	11/08/2025	HR	7.5	7.5
500	12/08/2025	HR	7.5	8.5
500	13/08/2025	HR	7.5	7
500	14/08/2025	HR	7.5	8
500	15/08/2025	HR	7.5	7.5
500	16/08/2025	HR	0
500	17/08/2025	HR	0
600	11/08/2025	HR	7	7
600	12/08/2025	HR	7	7
600	13/08/2025	HR	7	7
600	14/08/2025	HR	7	7.5
600	15/08/2025	HR	7	7.5
600	16/08/2025	HR	0
600	17/08/2025	HR	0
700	11/08/2025	Finance	7.5	2
700	11/08/2025	Finance	7.5	6
700	12/08/2025	Finance	7.5	2
700	12/08/2025	Finance	7.5	2.5
700	12/08/2025	Finance	7.5	1
700	13/08/2025	Finance	7.5	4
700	13/08/2025	Finance	7.5	1.1
700	13/08/2025	Finance	7.5	2
700	14/08/2025	Finance	7.5	7
700	15/08/2025	Finance	7.5	2.5
700	15/08/2025	Finance	7.5	4.5
700	16/08/2025	Finance	0
700	17/08/2025	Finance	0

Re: Not able to use measure in the legend of a bar chart. Any alternatives ?

v-pnaroju-msft — Wed, 20 Aug 2025 17:37:56 GMT

Hi grkanth81,

Thank you for your follow up.

Please find attached a screenshot and the revised sample .pbix file to address the issue:

We hope the information provided will help resolve the matter. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

Re: Not able to use measure in the legend of a bar chart. Any alternatives ?

v-pnaroju-msft — Sat, 23 Aug 2025 17:47:14 GMT

Hi grkanth81,

We would like to follow up and see whether the details we shared have resolved your problem.
If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

Thank you.

Re: Not able to use measure in the legend of a bar chart. Any alternatives ?

grkanth81 — Sun, 24 Aug 2025 22:18:39 GMT

Hi v-pnaroju-msft, sorry for the late reply. Yes that worked perfectly. Thanks again.