cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

X axis of visual not filtered by page filter

Hi,

i am applying a page filter to the Calendar table.

I would like to display visuals which partially ignore the filter applied to the date, so that, for example, I can select September (single month) and see in the visuals all the sales by month till September (with the X axis showing each month, not only September)

So summarizing how I can have a x axis which is not impacted by page filters applied to the same column of data?

thanks

Luca

1 ACCEPTED SOLUTION

@LucaA  I suggest you to create a YTD measure of the value you want to show in the chart and then use that measure in your chart 

1. You can create YTD measure, using the New Quick Measure option.

 

Number1 YTD 2 =
IF(
    ISFILTERED('Table_2'[Date]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    TOTALYTD(SUM('Table_2'[Number1]), 'Table_2'[Date].[Date])
)
 
2. Then you create another column for month in your date table like below
Month_Short = FORMAT(Table_2[Date].[Date],"mmm")
 
3. Then use newly created month measure on the Axis and YTD measure in the value field like below
 
Month axis.png
 
 



Did I answer your question? Mark my post as a solution!
Appreciate your Kudos


Proud to be a Super User!

Follow me here


View solution in original post

5 REPLIES 5
Super User IV
Super User IV

Tricky to do that exactly, but have you tried using an advanced filter on your Date column with is on or before and the date picker?

 

mahoneypat_0-1602942053937.png

 

Otherwise, I think you will need a disconnected table with your month values and/or need to build the logic into your measures.

 

Regards,

Pat

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


ty   @mahoneypat  but the user should be allowed to change the reference month.

Which is the best option between the 2 suggested (disconnected vs measure)? can you explain each one/the best ?

thanks

Luca

Page Filters affect all visuals on the page and this can't be changed. After all, this is what Page Filters are for....

@LucaA  I suggest you to create a YTD measure of the value you want to show in the chart and then use that measure in your chart 

1. You can create YTD measure, using the New Quick Measure option.

 

Number1 YTD 2 =
IF(
    ISFILTERED('Table_2'[Date]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    TOTALYTD(SUM('Table_2'[Number1]), 'Table_2'[Date].[Date])
)
 
2. Then you create another column for month in your date table like below
Month_Short = FORMAT(Table_2[Date].[Date],"mmm")
 
3. Then use newly created month measure on the Axis and YTD measure in the value field like below
 
Month axis.png
 
 



Did I answer your question? Mark my post as a solution!
Appreciate your Kudos


Proud to be a Super User!

Follow me here


View solution in original post

TY @negi007 . the output looks perfect.

Just 2 questions:

1) TOTALYTD should not give me the YTD value? (so if Jan is 1 and Feb is 5, in Feb i should have 6)

2) So the trick is to create another column for the month  (i already have one) in the same date table, why? i dont understand

thanks

Luca

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors