Use slicer value in X axis of line chart

[Guillaumeg]

Hi gurus,

I need help, I can't figure it out. I have read a lot of articles and I know it is not possible to use a measure as the X axis of a chart, but I can't believe there is no workaround.

Here my simple situation.

I have a table with Day number, Number of employee, e.g.:

Day Number	Number of employee
0	10
1	20
2	30
3	40
4	50
5	60
6	70
7	80
8	90
9	100
10	110

I have a slicer for the end user to choose the start date which is connected to a calendar table:

I use a measure to capture the selected date:

Start date selected = SELECTEDVALUE('Calendar'[Start Date])

Then I want to create a simple chart where  "Start date selected" = "Day Number = 0"

Of course I can create a simple measure that is working but I can't use it as X axis

X_Axis = Calendar[start date selected] + max(Table[Day Number])

Any recommandation?

thanks a lot

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file whether it suts your requirement.

expected result measure: =
VAR _startdate =
    SELECTEDVALUE ( 'Calendar start date slicer'[date_slicer] )
VAR _t =
    ADDCOLUMNS ( 'Day Number', "@xaxis", _startdate + 'Day Number'[Day Number] )
RETURN
    SUMMARIZE (
        FILTER ( _t, [@xaxis] = MAX ( 'Calendar x axis'[date_x_axis] ) ),
        'Day Number'[Number of employee]
    )

[Guillaumeg]

Hi again @Jihwan_Kim ,

If I can use few more minutes of your experience.

In reality, the number of employee needs a cap and there is multiple "Day Number" value (e.g. Day Number 1, value 10 and Day Number 1, value 20).

So at the end the is based on a measure:

Number of employee with MAX =

IF(

        SUM('Day Number'[Number of employee]) < 60

        ,SUM('Day Number'[Number of employee])

        ,60

    )

    * 0.6

I have modified your pbix by adding "Number of employee with MAX" but I can't add attachment to this forum...

How should I modify your "expected result measure:" formula to use this measure?

expected result measure: =

VAR _startdate =

    SELECTEDVALUE ( 'Calendar start date slicer'[date_slicer] )

VAR _t =

    ADDCOLUMNS ( 'Day Number', "@xaxis", _startdate + 'Day Number'[Day Number] )

RETURN

    SUMMARIZE (

        FILTER ( _t, [@xaxis] = MAX ( 'Calendar x axis'[date_x_axis] ) ),

        'Day Number'[Number of employee with MAX]

    )

Thanks again

Guillaume

[Jihwan_Kim]

Hi,

Please share your sample pbix file's link, and then I can try to look into it.

Thank you.

[Guillaumeg]

Hi @Jihwan_Kim 

Here is the link: dropbox

You will see the table is Projection_Curve.

The measure that is to be displayed on the graph is "5.#Patients_x_axis" (I used you code) but is is not working.

You can see also that the cumulative sum in measure "4.Patients_Cumulative" is not working also.

Thanks a million time.

Guillaume

[Jihwan_Kim]

Hi,

It is quite difficult for me to understand the logic, but I tried and please check the attached pbix file down below.

[Guillaumeg]

Hi @Jihwan_Kim 

If I can abuse of your time/expertise another time. I need to add a factor on the #patients after you plot the dates with the weeknumber (that you did). I tried to understand your code but I have to admit it is way above my competancies 😉

The link to the updated file is here

Basically after step "5.#Patients_x_axis 1" of the data "Projection_Curve Scenario1", I need to add a condition such as:

IF (MONTH(CalendarMondays_X_axis[Date]) = 1 THEN 'Projection_Curve Scenario1'[4.Patients_Cumulative 1] * 0.8 ELSE 'Projection_Curve Scenario1'[4.Patients_Cumulative 1])

How would this work to plot this in the line graph?

thank a lot as usual!!

Guillaume

[Guillaumeg]

Good morning @Jihwan_Kim ,

You nailed it!!!

This is exactly what I was looking for.

Thank you so much.

Guillaume

[Guillaumeg]

Hi @Jihwan_Kim ,

This is amazing, thank you for taking the time to answer. This is exactly what I wanted. You made my day!

Guillaume