Forum Discussion
Top N visual filter issue with large table
I have a wide table with 1M+ rows. The end user wants to display as many rows as possible in a simple table visual. I want to display the first 25,000 rows. If the number of rows returned is more than 25k, I only want to see the first 25k. It is is less than 25k, then just display the rows.
My table name name coupon_details and there is an [ID] column. I also have one other filter on the table for [InRange] =1
My problem is that when I apply a TopN filter based on the ID column, Top N is being applied to the table first. So, the [InRange] filter is applied on only the first 25k records. I need the order of this filtering swapped so [InRange] is applied first, then Top.
I have tried using a calculated column with rank and apply that to the Top filter, but it still did the same order of filtering. Any help is appreciated.
Hi _power_bi - Create a DAX measure that calculates the row number for each row by using below measure:
RowNumberInRange =
CALCULATE(
COUNTROWS(FILTER(coupon_details, coupon_details[InRange] = 1)),
FILTER(
ALLSELECTED(coupon_details),
coupon_details[ID] <= MAX(coupon_details[ID])
)
)This above measure calculates the row number for each row where [InRange] = 1, while preserving the filter context for other columns.
Create another measure to checks if the row number is less than or equal to 25,000 to returns 1 otherwise 0
FilteredRows =
IF([RowNumberInRange] <= 25000, 1, 0)apply the filtered rows on the visual level filter in your table chart and set it 1. It works . please check with your conditions
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
1 Reply
Hi _power_bi - Create a DAX measure that calculates the row number for each row by using below measure:
RowNumberInRange =
CALCULATE(
COUNTROWS(FILTER(coupon_details, coupon_details[InRange] = 1)),
FILTER(
ALLSELECTED(coupon_details),
coupon_details[ID] <= MAX(coupon_details[ID])
)
)This above measure calculates the row number for each row where [InRange] = 1, while preserving the filter context for other columns.
Create another measure to checks if the row number is less than or equal to 25,000 to returns 1 otherwise 0
FilteredRows =
IF([RowNumberInRange] <= 25000, 1, 0)apply the filtered rows on the visual level filter in your table chart and set it 1. It works . please check with your conditions
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!