Is it Possible to Use Top- N with Field Parameters? ✨Top N Filter with Field Parameter in Power BI
Vložit
- čas přidán 29. 03. 2023
- Since Field parameters in Power BI has multiple Fields, and We cannot use more than one Top N filters in our visual, So this video will explain, how to use a dynamic Top N filters with Field parameters
Get Help into Questions Related to #powerbi , #dax , #powerquery Power BI #datamodeling , #analysisservices and #powerbiservice
Check out our website www.powerbihelpline.com and blog-post. for end-to-end power BI solutions, Power BI templates, or any kind of Power BI questions like Power query errors, Authoring and optimizing DAX codes for complex business logic.
Follow us on LinkedIn
/ power-bi-helpline-22a2...
Email- info@pbihelpline.com
@PowerBIHelpline - Věda a technologie
Since I had been searching for the solution for a day, I am really grateful that I came across your video. Very informative!
You're welcome!
Thank you very much for the time spent, it has been a great help!
Glad it was helpful!
Very clear explanation,your effort and time much appreciated, thank you for the video ❤❤❤
Thank you so much for the appreciation and the feedback.
Great video - thank you for taking the time to post the solution!!
Glad it helped
Thanks a lot I was looking for this solution for a while!
Glad I could help!
Outstanding!!!! your tricks and tips helps a lot to me. Very Informative. Thank you so much, One more help is it possible to show TopN + others also in the same way. By selecting dynamic top N your measure and parameter gives the exact output, that is if we select 5 then we are getting the top 5 and the other customers are to be grouped as "Others" and need to sum all the value under others. if possible please post that video, will learn it from you.
Great to hear!
We have published videos about dynamic top N and TopN + others as well. Please check it out
Great idea and very clear explanation! Thank you for your video!
Glad it was helpful!
I really needed this solution, thank you.
You are most welcome
i love you man. you're my savior
You are a Darling, worked super smooth !
Great to hear! Thanks for waching
Thank you for this wonderful thing
Our pleasure!
thank you very good..
Our pleasure!
Thanks,the video was very helpful..can you suggest how can we do the same activity if vales are also a parameter..
Like if we have sales and qty
Hi! Love this method to show top N, many thanks! I've memory problems not allowing me to move on with this scenario. Query's not running with PRO license
When you are in local Power BI desktop, Memory problem is related to the RAM used from your computer. if you are getting, out of memory error for a particular visual, then you may need to recheck the DAX expressions used in it. Follow the best practices while authoring DAX expression or data model
Great!
Thanks!
👌 It's good 👍
Thank you for the feedback
Hi! thank you for this very informative Video. Question : I have a stacked bar line chart, bar shows values and line shows percentage. I have also used parameters so i can choose fields i want to show in my bar chart. But I am having issues with the top N values. For example I put a field in the legend then selected top 10, but it is showing me more than 10 items.
Thanks for watching.
Answer to your query - Did you use the technique used in this video?. you may need to check your Rank measure if it is giving correct ranking for those fields or not.
Thanks for the video, very helpful. My question is how to apply this to a multi-select slicer? For example, if you selected both product and customer name it get the ranking based on the combination, then what would need to be done?
Calculating ranking with combination of fields which comes from different table, will depend upon your semantic modeling in power bi.
Question about dynamic filter:
what if I want to rank by MoM Changes and MoM % changes besides absolute amount, how could I modify the DAX? create two more measures, one for MoM and second one for MoM%? thank you!
Dynamic Filter =
VAR _selectednumber = SELECTEDVALUE('Top N'[Top N])
RETURN
IF(
ISFILTERED('Top N'),
INT(
[Rank]
You should use && between each conditions. however, you can actually create a single measure which creates a combined rank, based on multiple measures. you can use new RANK() function.
I have posted a video on that. please check it out.
Amazing video! I'm using it to toggle between 3 different measures by 1 dimension, Channel. But, let's say I want to have a trended line chart with the same 3 measures to toggle between and the Channel as the series, but the Month dimension comes into the picture. Using this solution, I end up getting the top N individual data points for each month, rather than the top N channels over the 12 month span. Any thoughts here?
I realized that if I want to simply do a top 5, I can just filter that visual to Top 5 Channels by Rank. But what if I want to make this fully dynamic is the real question :)
Thanks for watching. The key here is to create the Ranking depending on your requirements. so you need to modify the Ranking measure. in this example RANKX() is using only Allselected Customers to assign the rank. you can modify to generate a proper rank for your scenario.
10/10
Thanks, @PowerBIHelpline
What would be the approach if you have multiple Kpis like Sales, Revenue, and losses?
No issue with multiple KPIs. but you need to decide on what basis you want the ranking to be created, if you want it be based on multiple conditions. you can also use new RANK() function
@@PowerBIHelpline every dimension per Kpi
@@samuelkiarie325 You can create a single measure by validating many different ways to create the final Rank measure. if needed you may create a measure table as well and then Utilise SWITCH() and SELECTEDVALUE() Function to validate each and every condition for various set of combinations in the final evaluation context.
Your video was perfect. Just missed one thing that i needed and believe me I tried a loooooot. How the hell do I transform the other values for the dimension into "other" and again sum the measure as it was done at the top 5.
Glad you liked it!I have posted other video related to show top 5 and Others. This Others is expandable in the Matrix visual to see what all products contributes to the "Other" Please check it out
Can you make the filters be measures selected in the parameter? is it possible?
Making DAX to works our way is not always straight forward. I have posted videos regarding using measures as filters. the hint is to make the measure to return true or false value for any expression, and then convert it to 0/1 using INT() then we can use it for visual level filters.
Rank value is not coming when we add into matrix
It should work in any visual, however we need to understand the how column headers and row headers are creating the evaluation context for any particular DAX
Not WOrking Getting Part Key Error
You may need to cross check your DAX expression for errors, and you should also look into the datamodel to understand more how your expression is working
I have a very odd bug where it works for some dimensions, but dont work for other dimensions. so
Rank =
SWITCH(
TRUE(),
SELECTEDVALUE('Dimension 2'[Dimension Fields]) = "'Distributor-Info-Lookups-VIP-SRS'[Dist Name]",
RANKX(
ALLSELECTED('Distributor-Info-Lookups-VIP-SRS'[Dist Name]),
[YTD TY],
,
DESC
),
SELECTEDVALUE('Dimension 2'[Dimension Fields]) = "'Distributor-Info-Lookups-VIP-SRS'[Distributor Parent]",
RANKX(
ALLSELECTED('Distributor-Info-Lookups-VIP-SRS'[Distributor Parent]),
[YTD TY],
,
DESC
),
SELECTEDVALUE('Dimension 2'[Dimension Fields]) = "'Distributor-Info-Lookups-VIP-SRS'[Business Unit]",
RANKX(
ALLSELECTED('Distributor-Info-Lookups-VIP-SRS'[Business Unit]),
[YTD TY],
,
DESC
),
SELECTEDVALUE('Dimension 2'[Dimension Fields]) = "'Distributor-Info-Lookups-VIP-SRS'[Region]",
RANKX(
ALLSELECTED('Distributor-Info-Lookups-VIP-SRS'[Region]),
[YTD TY],
,
DESC
),
SELECTEDVALUE('Dimension 2'[Dimension Fields]) = "'Distributor-Info-Lookups-VIP-SRS'[Market Managers]",
RANKX(
ALLSELECTED('Distributor-Info-Lookups-VIP-SRS'[Market Managers]),
[YTD TY],
,
DESC
),
SELECTEDVALUE('Dimension 2'[Dimension Fields]) = "'DSD Monthly Report V2'[Chains]",
RANKX(
ALLSELECTED('DSD Monthly Report V2'[Chains]),
[YTD TY],
,
DESC
),
SELECTEDVALUE('Dimension 2'[Dimension Fields]) = "'DSD Monthly Report V2'[Classes of Trade]",
RANKX(
ALLSELECTED('DSD Monthly Report V2'[Classes of Trade]),
[YTD TY],
,
DESC
),
SELECTEDVALUE('Dimension 2'[Dimension Fields]) = "'DSD Monthly Report V2'[Ultimate Owner]",
RANKX(
ALLSELECTED('DSD Monthly Report V2'[Ultimate Owner]),
[YTD TY],
,
DESC
),
SELECTEDVALUE('Dimension 2'[Dimension Fields]) = "'ODS_VIP_ITMXDA'[Package Size]",
RANKX(
ALLSELECTED('ODS_VIP_ITMXDA'[Package Size]),
[YTD TY],
,
DESC
),
SELECTEDVALUE('Dimension 2'[Dimension Fields]) = "'ODS_VIP_ITMXDA'[Product Types]",
RANKX(
ALLSELECTED('ODS_VIP_ITMXDA'[Product Types]),
[YTD TY],
,
DESC
),
SELECTEDVALUE('Dimension 2'[Dimension Fields]) = "'ODS_VIP_ITMXDA'[Flavors]",
RANKX(
ALLSELECTED('ODS_VIP_ITMXDA'[Flavors]),
[YTD TY],
,
DESC
),
SELECTEDVALUE('Dimension 2'[Dimension Fields]) = "'ODS_VIP_ITMXDA'[Item_Description]",
RANKX(
ALLSELECTED('ODS_VIP_ITMXDA'[Item_Description]),
[YTD TY],
,
DESC
)
)
It doesnt work for the Product types, distributor parents, flavors. I checked the table & column names and its not the problem. maybe you have a clue?
YTD TY =
CALCULATE(
sum('DSD Monthly Report V2'[Value]),
'DSD Monthly Report V2'[TY/LY]="TY",
'DSD Monthly Report V2'[MTD/YTD]="YTD"
)
Do you see any issue in your semantic model? you need to carefully look into the model to know weather these dimensions are accessible through the kind of relationships you have b/w the tables
@@PowerBIHelpline i had my senior BI analyst take a look at it, and we're both still stumped. idk maybe i need to break it down 1 by 1. i might just not mention it and wait if anyone realize haha
thank you always for your nobility please enoyer.pbix
Thanks for the feedback.