Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LucaA
Helper IV
Helper IV

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
negi007
Community Champion
Community Champion

@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 on linkedin

View solution in original post

5 REPLIES 5
mahoneypat
Employee
Employee

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....
negi007
Community Champion
Community Champion

@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 on linkedin

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.