Forum Discussion
How to use multiple Top N Filters!
Hi Everyone!
I am new to Power BI and am trying to build a dashboard to show each company's 2022 and 2023 sales data, but the business partner wants me to filter the 2022 and 2023 top 10 companies only! So in this case, if a company is in the top 10 in 2022 but not the top 10 in 2022 it should still show on the dashboard since Power BI does not support multiple Top N Filters is there any way to get what I want?
Both 2022 and 2023 sales values are measures I created based on the original data set, we have other information such as segments in the data set I also want to make sure that the ranking will work properly when we filter by different segments.
I have created a fake dada below just to make more sense!
Thank you so much for your help!
Hi Anonymous ,
Please try below steps:
1. below is my test table
Table:
2. create a measure with below dax formula
Measure = VAR tmp = FILTER ( ALL ( 'Table' ), YEAR ( [Date] ) = 2022 ) VAR tmp1 = FILTER ( ALL ( 'Table' ), YEAR ( [Date] ) = 2023 ) VAR _a = TOPN ( 2, tmp, [Sale], DESC ) VAR _b = TOPN ( 2, tmp1, [Sale], DESC ) VAR _c = SELECTCOLUMNS ( _a, "CN", [Company Name] ) VAR _d = SELECTCOLUMNS ( _b, "CN", [Company Name] ) VAR _e = UNION ( _c, _d ) VAR cur_cn = SELECTEDVALUE ( 'Table'[Company Name] ) RETURN IF ( cur_cn IN _e, 1 )3. add a table visual with Table fields ,add measure to table visual filter pane and set
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi Anonymous ,
Please try below steps:
1. below is my test table
Table:
2. create a measure with below dax formula
Measure = VAR tmp = FILTER ( ALL ( 'Table' ), YEAR ( [Date] ) = 2022 ) VAR tmp1 = FILTER ( ALL ( 'Table' ), YEAR ( [Date] ) = 2023 ) VAR _a = TOPN ( 2, tmp, [Sale], DESC ) VAR _b = TOPN ( 2, tmp1, [Sale], DESC ) VAR _c = SELECTCOLUMNS ( _a, "CN", [Company Name] ) VAR _d = SELECTCOLUMNS ( _b, "CN", [Company Name] ) VAR _e = UNION ( _c, _d ) VAR cur_cn = SELECTEDVALUE ( 'Table'[Company Name] ) RETURN IF ( cur_cn IN _e, 1 )3. add a table visual with Table fields ,add measure to table visual filter pane and set
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.