Not getting all the values for legend

[PowerOn_DP]

Hello experts,

After a lot of tries I dont understand how can I get all the values from the measure for my bar chart legend

Below is the measure I am trying to create to return lenght of stay in months 

Rx Cummulative Length of Stay =

Var FromDate = MIN(DimCalendar[Date])

Var LengthoFStay = SUMX(MyPT_CurrentCustomers, DATEDIFF(MyPT_CurrentCustomers[Started Trial (UTC)], FromDate, MONTH))

Return

SWITCH

(

TRUE(),

LengthoFStay>=0 && LengthoFStay<13,"0 to 12 months stay",

LengthoFStay>=13 && LengthoFStay<25,"13 to 24 months stay",

LengthoFStay>=25,"Over 25 months stay"

)

I am trying to get this one

Any help will be much appreciated

thanks

[PowerOn_DP]

@amitchandak 

I really appreciate your help.

[amitchandak · Accepted solution]

@PowerOn_DP , You can not bucket like this on the measure.

Please check this video on bucketing on measure. It requires an independent table and new measures

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

If this does not help

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[PowerOn_DP]

Thanks for sharing @amitchandak 

Currently my bar chart looks like below

For which I had a created a belo custom column using dax with fixed column and Today() function to difine the lenght of stay category which helped me to put it on the bar chart nicely.

Length of Stay Category =

Var LengthoFStay =DATEDIFF(MyPT_CurrentCustomers[Started Trial (UTC)],TODAY(),MONTH)

Return

SWITCH

(

TRUE(),

LengthoFStay>=0 && LengthoFStay<13,"0 to 12 months stay",

LengthoFStay>=13 && LengthoFStay<25,"13 to 24 months stay",

LengthoFStay>=25,"Over 25 months stay"

)

Now, the change is instead of calculating length of stay from [Started Trial (UTC)] to TODAY(), I need to use Min(DimCalendar[date]) which returns me the start date of each month on bar chart.

I have no idea if this measure is going to work.

[PowerOn_DP]

@V-lianl-msft @amitchandak Please help