Dynamic filter for slicer (for 150,000 row download limit)

[PowerWhy]

Hello,

I have created this test file for downloading 150,000 rows at a time. I have a filter to choose which page to display for download - please can you tell me how to make the page filter dynamic to only display the number of pages that data is available for? There are about 1 million rows so it should offer 7 pages for download. (It should also respect the other 2 slicers at the top of the page)

Thank you for any pointers in the right direction! 🙂

PW

PBIX file:

https://www.dropbox.com/s/9q5jbo7e71s45nz/Test%20-%20150%2C000%20records%20download%20limit.pbix?dl=... 

[v-jayw-msft · Accepted solution]

Hi @PowerWhy ,

Looks like the [RankSelectedMax] is a dynamic value based on other 2 slicers.

Then you could create a measure like below.

pages =
ROUNDUP ( MAXX ( ALL ( 'table' ), [index] ) / [RankSelectedMax], 0 )

Best Regards,

Jay

[PowerWhy]

Thank you @jdbuchanan71 , but I don't think that would reflect the user's selections in the slicers circled in blue? 

[jdbuchanan71]

@PowerWhy 

It looks like you have an INDEX column on your data.  You could just add a Page column like this.

Page = INT ( DIVIDE ( 'Test journal lines'[Index], 150000 ) ) +1

The +1 is there to stop it from showing Page 0

Then put that column on your slicer.