Date slicer filter only one value in graphic and does not affect x axis

[Anonymous]

Hi All,

I have a dashboard like this : 

The graph is filtered by date slicer.

If I select for example january to May on the slicer, it will filter the graph : 

My question is simple : 

is it possible to select january to may in the slicer and have the x axis from January to December and the line from January to December too? 

But the bars will be filtered (from January to May).

Thank you in advance 😉

Best regards

[Anonymous · Accepted solution]

Hi@Anonymous,

Hi@ADN75,

You can create a new date dimension table to help you achieve this requirement.

1. Create a new table:

Date Table = VALUES('Table'[Month])

2. Create new measure named New Sales an New Profit.

New Sales = 

IF(

MAX('Date Table'[Month]) in ALLSELECTED('Table'[Month]),

CALCULATE(MAX('Table'[Sales]),FILTER('Table','Table'[Month]=MAX('Date Table'[Month])))

)

New Profit = 

IF(

MAX('Date Table'[Month]) in ALLSELECTED('Table'[Month]),

CALCULATE(MAX('Table'[Profit]),FILTER('Table','Table'[Month]=MAX('Date Table'[Month]))),

0

)

Put the New Profit and New Sales instead of the original one.

Then you can use the new “month” in Date table to interact with the visual.

You will get the result like this:

Here is the demo, please try it.

If this does not solve your problem, please provide more details and be free to let me know.

Hope it helps.

Best Regards,

Caitlyn Yan

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous 

thank you for your help ! 

"Profit" and "Sales" are measures in another table... so I didn't manage to use them in your DAX formula..

[Anonymous]

Hi @Anonymous，

If another table already has a Month column and you want to use it as the x-axis, you can pull that column in my measure instead.

Or would you mind providing me with a pbix file without sensitive information so that I can help you better?

Best Regards,

Caitlyn Yan

[Anonymous]

Hi @Anonymous ,

Sorry, it's in French, my problem is GRAPH_A expected Production Wind is a measure and the error message is : column GRAPH_A expected Production Wind in table Reporting is untraceable and isn't used in this expression.

Unfornatelly, I can attach pbix file to my message. Maybe I can send you a pbix by mail?

[Anonymous]

Hi@Anonymous ，

I checked your error message and because you are referencing a measure in the formula, my formula does not work for you.

For your data security, etc., we only support to solve the problem for you on the forum, you can refer to the following article:How to provide sample data in the Power BI Forum 

Best Regards,

Caitlyn Yan

[amitchandak]

@Anonymous , if the month is coming from a different table. Try this option or add +0

[Anonymous]

Hello,

A bit more explaination : 

I managed to set the date axis from January to December (even if this is not the best solution because if I change the year, I need to change in the Format option of my graph too) : 

On this visual, I just want the line (Profit) from January to December (not from January to July like in the date slicer).

Is it possible?

Thank you

[Anonymous]

Hi @amitchandak,

The month comes from the same table unfortunatelly. And there are data from May to December for the value I want to show...