cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ADN75
Frequent Visitor

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

Hi All,

 

I have a dashboard like this : 

ADN75_0-1628186672131.png

The graph is filtered by date slicer.

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

ADN75_1-1628186902461.png

 

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

 

1 ACCEPTED SOLUTION
v-caitlyn-mstf
Community Support
Community Support

Hi@ADN75,

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])

 

  1. 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:

vcaitlynmstf_1-1628737268893.png

 

 

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.

 

View solution in original post

8 REPLIES 8
v-caitlyn-mstf
Community Support
Community Support

Hi@ADN75,

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])

 

  1. 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:

vcaitlynmstf_1-1628737268893.png

 

 

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.

 

Hi @v-caitlyn-mstf 

 

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..

Hi @ADN75

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

Hi @v-caitlyn-mstf ,

 

ADN75_0-1628758961909.png

 

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?

Hi@ADN75

 

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
Super User
Super User

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

 

ShowItemwithoutdata.JPG

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

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) : 

ADN75_0-1628272590389.png

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

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...

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!