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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
alevandenes
Helper IV
Helper IV

Static and changeable column in same histograph

Hi Community,

 

I am trying to figure out whether it is possible to have a static column and a "changeable" one in the same graph. 

To better explain i would for the column with the BLUE arrow to be static (always display data for the last 2 years) and for the column with the RED arrow to be changing based on a filter slicer (i could select for example this quarter or last quarter data).

alevandenes_0-1659526572473.png

do you know whether this is even possible?

Thanks in advance!

1 ACCEPTED SOLUTION
rbriga
Impactful Individual
Impactful Individual

Let's assume you are comparing duration, and you have a duration measure (the red arrow, dynamic).

You need to create a second, calendar -resistant measure:

Duration (Last 2 Years)=
VAR _MaxYear= 
CALCULATE(
 MAX('Calendar'[Year]),
 REMOVEFILTERS()
)
RETURN
CALCULATE(
 [Duration],
 REMOVEFILTERS('Calendar'),
 KEEPFILTERS('Calendar'[Year] => (_MaxYear-1))
)
-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!

View solution in original post

6 REPLIES 6
rbriga
Impactful Individual
Impactful Individual

Let's assume you are comparing duration, and you have a duration measure (the red arrow, dynamic).

You need to create a second, calendar -resistant measure:

Duration (Last 2 Years)=
VAR _MaxYear= 
CALCULATE(
 MAX('Calendar'[Year]),
 REMOVEFILTERS()
)
RETURN
CALCULATE(
 [Duration],
 REMOVEFILTERS('Calendar'),
 KEEPFILTERS('Calendar'[Year] => (_MaxYear-1))
)
-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!

somehow it doesnt exactly work for me.

does the first REMOVEFILTER formula have to be empty?

Okay, it works with the filled REMOVEFILTERS!! thanks a lot!

SpartaBI
Community Champion
Community Champion

@alevandenes so you need one measure that always show the last 2 years and one that is changeable. Or you can't use 2 measures and must put a label?

i think i can use two measures. just not sure how to 😕

@rbriga already gave you one and the other one is the just the simple aggregatiom that will be responsive to any filter context including from the date slicer

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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