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
carlol
Helper IV
Helper IV

Filter to always show one value irrespective of the filter selection

 

Hi 

 

Is it possible to have the visual always to show 2019 regardless of filter Selection?

 

Capture.PNG

1 ACCEPTED SOLUTION

Hi @carlol ,

Please try to change the type of X-axis from continuous to categorical, it should work.

x-axis.png

 

Best Regards,
Yingjie Li

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

9 REPLIES 9
v-yingjl
Community Support
Community Support

Hi @carlol ,

You can create a seprate year table and create this measure:

 

Year = DISTINCT('Table'[Year])
Measure =
IF (
    SELECTEDVALUE ( 'Table'[Year] ) = 2019,
    SUM ( 'Table'[Value] ),
    IF (
        SELECTEDVALUE ( 'Table'[Year] ) IN DISTINCT ( 'Year'[Year] ),
        SUM ( 'Table'[Value] )
    )
)

 

slicer.png

Attached a sample file in the below that you can refer, hopes to help you.

 

Best Regards,
Yingjie Li

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

Thanks

 

But that is not what I want 

If a User Selects 2009 in the Slicer , I want to illustrate 2009 and 2010 Data Only

If a User Selects 2008 in the Slicer , I want to illustrate 2008 and 2010 Data Only

 

Ideally the Year table should be 

 

Year =

Union(ALLSELECTED('Year'[Year]),ROW("Year","2010")) but based on  SELECTEDVALUE ('Year'[Year])

 

 

This Should work

 

VAR _Sel = Union(ALLSELECTED('IHFD vwKPI'[HospID]),ROW("HospID","9999"))
Return
CALCULATE(Round(Divide(SUM('IHFD vwKPI'[Numerator]),SUM('IHFD vwKPI'[Denominator])),2),FILTER(ALL('IHFD vwKPI'),'IHFD vwKPI'[HospID] in _Sel)
)

Hi @carlol ,

"If a User Selects 2009 in the Slicer , I want to illustrate 2009 and 2010 Data Only

If a User Selects 2008 in the Slicer , I want to illustrate 2008 and 2010 Data Only"

 

In the sample, you can set the slicer selection as single in the selction controls to only selcet one year value.

year slicer.png

 

Best Regards,
Yingjie Li

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

 

If a User Selects 2016 in the Slicer ,  it illustrates 2016,2017,2018,2019

If a User Selects 2017 in the Slicer ,  it illustrates 2017,2018,2019

 

If as user selects 2016 , you cannot have 2017 illustrating zero , if a user selects 2017 , 20 is illustrated 

 

The X axis needs to just illustrate the data points Selected ,

 

If a User Selects 2016 in the Slicer ,

2016 and 2019 is only illustrated

 

If a User Selects 2017 in the Slicer ,

2017 and 2019 is only illustrated

 

 

Hi @carlol ,

Please try to change the type of X-axis from continuous to categorical, it should work.

x-axis.png

 

Best Regards,
Yingjie Li

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

Thanks Yingjie Li, was not aware of that feature 

amitchandak
Super User
Super User

@carlol ,

Better to have separate Year table for this

 

measure =
var _Sel = union(allselected(Year[Year]),row("Year",2009))
return
calculate([measure] , filter(all(Year), Year[Year] in _sel))

Few Attempts , struggling

 

I want it to include the filter selection and 9999

 

measure =
var _Sel = union(allselected(('IHFD DimHospital'[HospID]),row("HospID" ,9999))
return
calculate('IHFD vwKPI'[Average] , filter(all('IHFD DimHospital'), 'IHFD DimHospital'[HospID] in _sel))
 
measure =
VAR _Sel = SELECTEDVALUE ('IHFD DimHospital'[Hospital])
VAR _Table = CALCULATETABLE ( VALUES ('IHFD DimHospital'[HospID]),'IHFD DimHospital'[Hospital] = _Sel)
RETURN
CALCULATE ('IHFD vwKPI'[%] ,FILTER(ALL(_Table),'IHFD DimHospital'[HospID] in _Sel))
 
measure =
VAR _Sel = SELECTEDVALUE ('IHFD DimHospital'[Hospital])
VAR _Table = CALCULATETABLE ( VALUES ('IHFD DimHospital'[HospID]),'IHFD DimHospital'[Hospital] = _Sel)
RETURN
CALCULATE ('IHFD vwKPI'[%] ,_Table, KEEPFILTERS ('IHFD DimHospital'[HospID]= "9999" || 'IHFD DimHospital'[Hospital] = _Sel ))
 

measure = var _Sel = union(allselected('IHFD vwKPI'[Year]),row("Year",2009))
return
calculate('IHFD vwKPI'[Average], filter(all('IHFD vwKPI'[Year]),'IHFD vwKPI'[Year] in _Sel))
 
I select 2017 , Average of 2017 is only illustrated , am I doing something wrong?

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.