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
Sara16
Helper III
Helper III

Fixed line in line graph

Hi peeps

 

I have a link graph which is affected by an array of slicers however I do not want one of the lines to be changed when slicers are changes. My example is in the below. I want the "DPS Average" to not change but the others to change - HELP!

Sara16_0-1656555975992.png

 

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @Sara16 ,

In your fomula below, you add a extra symbol "{", it caused the error.

vkalyjmsft_0-1657008295806.png

Additonally, you should use the allexcept function, otherwise the fixed line value is the same for each date.

If the Axis of the line chart is [Date], modify the formula like this:

Fixed Line =
CALCULATE (
    [DSP Average],
    ALLEXCEPT ( 'Unscheduled Leave', 'Unscheduled Leave'[Date] )
)

Get the correct result in my sample.

vkalyjmsft_0-1657008929263.png

vkalyjmsft_1-1657008956545.png

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ kalyj

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

13 REPLIES 13
Sara16
Helper III
Helper III

Apologies if I was not clear.

The measure I have above is the one I have duplicated. So Ill have both in the same graph but for one measure I want it to be affected (area) by the slicers and the other I do not want it to be affected. So when I use the Calculate[DPS Average] all()) for one measure it still is affected by the slicers.  

Hi @Sara16 ,

Sorry for the late back. How about using different measures separately, not to duplicate it. One using the original measure and another using the Fixed Line.

Best Regards,
Community Support Team _ kalyj

Thanks Kalyj - unfortunately still doesnt work. Not duplicating the measure but duplicating the code for the measure but then creating a measure based on the code you have supplied. 

 

So to explain I have the main measure of 

Average Days Per FTE - rolling 12 Months =
VAR STARTDATE =
    CALCULATE ( STARTOFMONTH('Unscheduled Leave'[Cal. year / month]) )
RETURN
    CALCULATE (
        DIVIDE ( SUM ( 'Unscheduled Leave'[Actual Days]), SUM ( 'Employee Master'[FTE - Operational] ) ),
        DATESINPERIOD ( 'Report Date'[Date], STARTDATE, -12, MONTH)
        )*12
 
Then I have duplicated this code for DPS Average. But to get DPS average I used your code 
DPS Average = CALCULATE (
    [DPS Average Days Per FTE - Measure],
    ALLEXCEPT ( 'Report Date', 'Report Date'[Date] )
). 
 
Unfortunately any time you change the slicers it changes everything

Hi @Sara16 ,

For example column in the slicer is [Category], try to modify the code:

DPS Average =
CALCULATE ( [DPS Average Days Per FTE - Measure], ALL ( [Category] ) )

Best Regards,
Community Support Team _ kalyj

Thankyou Kalyj - this works well - however if I want multiple categories to be ignored, a simple comma doesnt seem to work? 

Sara16
Helper III
Helper III

Unfortunately this still isnt working 😞 I have duplicated the meausre as one measure needs to capture all (with as you mentioned above the parenthesis) and one that is affected by the slicer but it picks up for both

 

My measure is 

Average Days Per FTE - rolling 12 Months =
VAR STARTDATE =
CALCULATE ( STARTOFMONTH('Unscheduled Leave'[Cal. year / month]) )
RETURN
CALCULATE (
DIVIDE ( SUM ( 'Unscheduled Leave'[Actual Days]), SUM ( 'Employee Master'[FTE - Operational] ) ),
DATESINPERIOD ( 'Report Date'[Date], STARTDATE, -12, MONTH)
)*12
 
 

Hi @Sara16 ,

Sorry I'm not very clear "one measure needs to capture all" and "one that is affected by the slicer but it picks up for both".

In your original post, you want the "DPS Average" to not change but the others to change, so when you replace the DPS Average with the Fixed Line, what happend? Does it still change by the slicer?

 

Best Regards,
Community Support Team _ kalyj

So what I mean is that I have three slicers - Division, Branch Section. I have the same formula for two meausres. One i want all Division, Branch section to be fixed and not changed. But my slicers (Division, Branch and Section) change the other measure. Does that make sense? 

v-yanjiang-msft
Community Support
Community Support

Hi @Sara16 ,

In your fomula below, you add a extra symbol "{", it caused the error.

vkalyjmsft_0-1657008295806.png

Additonally, you should use the allexcept function, otherwise the fixed line value is the same for each date.

If the Axis of the line chart is [Date], modify the formula like this:

Fixed Line =
CALCULATE (
    [DSP Average],
    ALLEXCEPT ( 'Unscheduled Leave', 'Unscheduled Leave'[Date] )
)

Get the correct result in my sample.

vkalyjmsft_0-1657008929263.png

vkalyjmsft_1-1657008956545.png

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ kalyj

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

amitchandak
Super User
Super User

@Sara16 . Create a line on a measure like 

 

calculate([Measure], all() )

 

This will ignore all the filter

Thankyou I tried this one and still fails - see error message below

 

Sara16_0-1656560704207.png

 

@Sara16 , parenthesis after calculate are two, there should be one

 

calculate([DPS Average], all() )

 

or

 

calculate([DPS Average], all('Unscheduled Leave') )

Unfortunately still doesnt work 😞 is it because its the same measure but I am trying to fix one of the measures? 

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.

Top Solution Authors