Use Measure as chart legend in Direct Query Mode

[Guille]

Hi everybody,

have hit a roadblock and would appreciate your help on this. I am aware of limitations in DAX when using Direct Query mode. I have a table with the following columns among many more

Purchase Order	Date Received	Date Completed	Warehouse-ID
PO1	11/21/2017	11/24/2017	B
PO2	11/20/2017	11/20/2017	A
PO3	11/24/2017	11/28/2017	B
PO4	11/20/2017	11/22/2017	A
PO5	11/22/2017	11/24/2017	A

I need to calculate the number of days between these two dates, but only working days, excluding weekends and company holidays, which are not all federal holidays, but specific holidays in the year.

For warehouse A the Purchase order must be processed in one day maximum to meet the criteria. And for warehouse B they must be processed in two days maximum. In the sample above, PO1 meet criteria because 11/23/2017 was a holiday (thanksgiving) so it took only two days, PO3 meets criteria because there is a weekend between the two dates, PO4 took two days and doesn’t meet the criteria for warehouse A, but PO5 meets the criteria because of the holiday, so it only took one day to process.

Based on the number of days between these two dates and the warehouse-ID, I must count the total purchase orders that meet the criteria and the total that don’t meet the criteria, and represented in a bar chart like this:

I created measures to calculate the difference between dates with the help of a calendar table that includes the holidays, and created a measure that get values “Meet Criteria” and “Don’t Meet criteria” based on that difference, but can’t use that measure as legend in the chart. I could not use Calculated columns. Any help would be highly appreciated.

[v-yulgu-msft · Accepted solution]

Hi @Guille,

It is not possible to add a measure as chart legend, as measure actually returns a single value. We can only add a column into legend section.

In your scenario, I would suggest you create two separately measures for “Meet Criteria” and “Don’t Meet criteria”. Add these two measures into Values section of the chart.

Regards,

Yuliana Gu

[deepavaishnava]

Thank you so much. I was stuck in a similiar scenerio from past 2 days. After reading this, got it resolved.

[Guille]

Thank you so much Yuliana, I noted PBI does not allow to add the measure in the legend section, but was not clear why, now I understand. I will try your suggestion to see how this can be represented in a visual. Thanks again.