Top N Slicer Fiscal Year

[PowerRocky12]

I have a fiscal year rank in my Date calender, which filters my visuals based on less then or equal to in the slicer. I am wondering if its possible to have the same outcome but without the "1" box. 

I create a Top N table "TopN = GENERATESERIES(0, 20, 1)" which gives me the single value field I am looking for but it filters only a single value. For example, if I selected 2, it would give me the 2nd highest rank fiscal year. Is there any Dax I can write where I could select a single value using my Top N slicer and it would give me that fiscal year and anything less then or equal to?

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Sales measure: = 
SUM(Sales[Sales])

TonNsales: = 
VAR _selecttopn =
    MAX ( 'TopN Table'[Value] )
VAR _currentyear =
    MAX ( 'Year'[Year] )
VAR _alltableperyear =
    FILTER ( ALL ( Sales ), Sales[Year] = _currentyear )
VAR _result =
    CALCULATE (
        [Sales measure:],
        KEEPFILTERS ( TOPN ( _selecttopn, _alltableperyear, [Sales measure:], DESC ) )
    )
RETURN
    IF ( HASONEVALUE ( 'Year'[Year] ), _result )

[jennratten]

The DAX function TOPN is designed to return the top N values as you are describing.  For example, if 2 is selected, then TOPN would return 1 and 2. 

Here is a good example of the implementation:

https://www.sqlbi.com/articles/showing-the-top-5-products-and-others-row/ 

Link to the documentation on TOPN:

https://docs.microsoft.com/en-us/dax/topn-function-dax 

If you need additional assistance, please post your DAX script here so we can see what you are doing. 

[PowerRocky12]

I created a topn table TopN = GENERATESERIES(0, 20, 1) and used this to create a slicer and want to use this to filter my fiscal years in my charts. I have fiscal yer ranked on my calendar but it only returns the FY indicted in the topn slicer so if I enter 5 it would show the fiscal year 5 years back. I want it to show fiscal years for a range of 1-5.

[v-chenwuz-msft]

Hi @PowerRocky12 ,

Is this you want ?

If yse, please try this measure and put it in filter pane of this column chart and set it show when value is 1.

Measure = IF( SELECTEDVALUE('Calendar'[FY])<=MAX('TopN'[FY Slicer]),1)

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[PowerRocky12]

Thank you but i already have it working like this. I am trying to only have a single input box and remove the other. Is this possible? 

[jennratten]

Yes, it is.  You should be able to remove the first slicer without any issues.  Can you please try and see if it still works?

[PowerRocky12]

No luck, what would be this step?