You are by far the best Power BI teacher I've found on UA-cam. Your examples are well thought, and I like it that you walk through the mistakes that anyone would likely face when building measures or visuals. Keep it up :)
Great video on my bugbear function, also thanks for reminding us all about wrapping the Sum in calculate if not using a measure, it's one of those really important things and could have a video in it's own right as to when and when not necessary.
Out of all UA-camrs on PowerBI, I love your explanations most! Question, if you wanted to select the top 2, but exclude 1 specific employee (for example, exclude Sally Grant; 12k), how would that be done? In my case, I have a 'other' category, which I never want to have shown.
Your ideas and way of explaining are awesome! Instead of using parameter for top 10 filtering , whether adding slicer with column ( employee ranked based on salary) would also work? The slicer can filtered for 10 values.
hey, in explained scenario in the video, isn't 'IF' function working in reverse, as 'IF' condition is if employee rank is >= 20 then return 1 and in filter we add "is =1" which then filters ranking >= 20 and return all the values.
good video but unless im missing something there was nothing to limit the TopN to the NUMBER of records I wasn't to display which was the primary reason for coming here as the title suggests...
hey, in explained scenario in the video, isn't If function working in reverse, as 'IF' condition is if employee rank is >= 20 then return 1 and in filter we add "is =1" which then filters ranking >= 20 and return all the values.
@@HowtoPowerBI no worries. I think the topN funcfion can indeed be very confusing especially when thinking about rank . That's why I wanted to dedicate a video to it as well. In th
Thank you for the video, great content. Could you also please do a video on showing a random set of rows from a table? Like if I have 1000 records for the given context and I need to see 10 random rows, not based on any ranking, so that the next time I need to see 10 rows, they'll be different from the ones I saw earlier.
Awesome videos, great content everytime, kudos to that!!!! Although I have a question, what if we want to see the rank of a department also in the same visual? could you please throw some light on that.
Hey Bas, Thank You for your videos. I'm a new subscriber and am really enjoying the way you teach DAX. Any thoughts on the New "Upgraded" DAX Intellisense that came out with the latest PBI Update ? ;)
thx Raj! Let me clarify TopN% -> If you want to return those employees that earn more than the median salary - that would be top 50% .. in the video I make that a bit more flexible and you could also choose any % , for example top10% employees based on salary
In my case, i need Top 20 and Bottom 20 customers based on their NetSales when i select slicer "Top 20 Customers" and Bottom 20 Customers" respectively. can this be achived with explained approach in the video? please share your insight why Below measure is not working CustomerTopNfilter = VAR x = IF ( [CustomerRanking] [Measure which returns x value when Bottom 20 Customer slicer is seleceted],0 ) ) return x
Hey Bas, Your videos are really helping me in my work. I have to project Bottom 10 or 20 in my project but it should exclude zero values. How can it be done? Your help would be really appreciated.
I have a similar issue but in my case i really have to calculate with only 10 in case of TOP10, if i take your example how could you calculate the average salary of the 10 best payed employee?
Will this approach work if my measure (total salary in your example) is a product function of two column from different tables. Confused what table should I refer while writing rankx. #howtopowerbi
Is there a way to have top n filter to ingore a product and move to next one. Like if i want to not include product 3 but instead want 4 then 5 etc . Is this possible
Hi Bas, Thank you for amazing videos. I need one urgent help. I want 3 card visuals in my report. First will show highest selling brand along with sales amount, second will give second highest selling Brand along with sales amount and similarly for third. I tried a lot but no luck. Can you pls guide me here with measure or anything. Thank you!
In the same vein of difficulty selecting things, if I have a measure that is limited by a date range, and I want to show all the dates in the range even if the measure has a value of zero, how do I go about that? All my attempts end up with "all dates" in my date table
Found a solution but I don't like it too much measurename = vars and stuff var firstdate = var lastdate = return measure calculation + if (min(calendar date) = firstdate, 0, BLANK() )
Hi Roberto, thx for watching! They are not the same -> When used inside of the calculate function both ALL and REMOVEFILTERS are the same. ALLSELECTED would remove the filters from the visual, but not outside filters (from a slicer, filter panel or other visual). In the rankx function I use the all function to return all the rows in a table, or all the values in a column, ignoring filters, and allselected if you want to keep filters from outside the visual. The rankx function then iterates over that table. REMOVEFILTERS can only be used to clear filters but not to return a table like all and allselected, so therefore wouldnt be an option here.
RankByAmountFails = RANKX(ALLSELECTED(DimCustomer),SUM(DimCustomer[Amount])) What is wrong with this one? It is still giving me always 1 for ranking although the amounts are different. E.g. the following is working as supposed ... RankyByBirthdayWorking = RANKX(ALLSELECTED(DimCustomer),FIRSTDATE(DimCustomer[BirthDate])) Edit: Ok, I see using Calculate or using a Mesure for Sum is the way to go again here .... Edit2: Ok, I should first watch videos till the end :D
You are by far the best Power BI teacher I've found on UA-cam. Your examples are well thought, and I like it that you walk through the mistakes that anyone would likely face when building measures or visuals. Keep it up :)
Then you not seen video of excelisfun guys
Totaly agree.
Great video on my bugbear function, also thanks for reminding us all about wrapping the Sum in calculate if not using a measure, it's one of those really important things and could have a video in it's own right as to when and when not necessary.
TopN confusion is "Ranked" high in my all dax confusions. 😊
Haha 😄 i guess you are not the only one 😉
I am subscribing to your page after this example. Exactly what I was looking for including the "ALLSELECTED" based on multiple key fields. Thank you!
Out of all UA-camrs on PowerBI, I love your explanations most! Question, if you wanted to select the top 2, but exclude 1 specific employee (for example, exclude Sally Grant; 12k), how would that be done? In my case, I have a 'other' category, which I never want to have shown.
You are always my savior. I always find solution for my PowerBi problems.
I discovered this channel only now. Very useful. Thanks for your work.
Your ideas and way of explaining are awesome! Instead of using parameter for top 10 filtering , whether adding slicer with column ( employee ranked based on salary) would also work? The slicer can filtered for 10 values.
Nicely explained, now no confusion. I like all your videos. Those are very helpful!!! Thanks!!!
thank you for watching Sanket!!! 🙂
One more clear and useful video, I make it a point to use most of these whenever required, really helpful for many of us. Thanks
Great to hear! thx Viraj!!! 😀
you solved my problem in 9 seconds.
That was art! this open my mind to do some very cool things on my reports. thanks!
Awesome 👊 ! Haha art 🎨 , too much credit 🤣 .. thx Daniel!!!
You helped me a lot! Thank you so much for this amazing content!
hey, in explained scenario in the video, isn't 'IF' function working in reverse, as 'IF' condition is if employee rank is >= 20 then return 1 and in filter we add "is =1" which then filters ranking >= 20 and return all the values.
good video but unless im missing something there was nothing to limit the TopN to the NUMBER of records I wasn't to display which was the primary reason for coming here as the title suggests...
hey, in explained scenario in the video, isn't If function working in reverse, as 'IF' condition is if employee rank is >= 20 then return 1 and in filter we add "is =1" which then filters ranking >= 20 and return all the values.
Thanks for sharing
thx for watching Mehdi 😀
This is very useful for me and lots of other people!!
So nice to hear, thx for watching 😊
@@HowtoPowerBI no worries. I think the topN funcfion can indeed be very confusing especially when thinking about rank . That's why I wanted to dedicate a video to it as well. In th
@@HowtoPowerBI please help, how to count % of running total - total qty..
very helpful, thx!
Glad to hear that! thx Matthias! 😀
Thank you for the video, great content. Could you also please do a video on showing a random set of rows from a table?
Like if I have 1000 records for the given context and I need to see 10 random rows, not based on any ranking, so that the next time I need to see 10 rows, they'll be different from the ones I saw earlier.
thanks for the Idea Gulmohar! sounds interesting, what is the practical application you need it for?
so touching for an excellent video
Wow again with new amazing video bas
Thx so much Ashish 😀
Awesome videos, great content everytime, kudos to that!!!!
Although I have a question, what if we want to see the rank of a department also in the same visual?
could you please throw some light on that.
will do, that topic deserves a video on its own
Hey Bas,
Thank You for your videos. I'm a new subscriber and am really enjoying the way you teach DAX.
Any thoughts on the New "Upgraded" DAX Intellisense that came out with the latest PBI Update ? ;)
useful video thnks fr that, bt i did not understand % concept
thx Raj! Let me clarify TopN% -> If you want to return those employees that earn more than the median salary - that would be top 50% .. in the video I make that a bit more flexible and you could also choose any % , for example top10% employees based on salary
This was very helpful, thx
😀 thanks for watching Jacob
In my case, i need Top 20 and Bottom 20 customers based on their NetSales when i select slicer "Top 20 Customers" and Bottom 20 Customers" respectively. can this be achived with explained approach in the video?
please share your insight why Below measure is not working
CustomerTopNfilter =
VAR x = IF
( [CustomerRanking] [Measure which returns x value when Bottom 20 Customer slicer is seleceted],0
)
)
return
x
Hey Bas,
Your videos are really helping me in my work. I have to project Bottom 10 or 20 in my project but it should exclude zero values. How can it be done?
Your help would be really appreciated.
Can anyone explain me how to obtain the right total of the topn rows?
You are an amazing teacher!
Nice video... Btw, I was expecting a solution so the Top3 would return 3 items =/
RANKX function is not throwing expected outcome. Happy to share my code if you like. Any help will be highly appreciable.
hello
I have a similar issue but in my case i really have to calculate with only 10 in case of TOP10, if i take your example how could you calculate the average salary of the 10 best payed employee?
Will this approach work if my measure (total salary in your example) is a product function of two column from different tables. Confused what table should I refer while writing rankx.
#howtopowerbi
so clear
Is there a way to have top n filter to ingore a product and move to next one. Like if i want to not include product 3 but instead want 4 then 5 etc . Is this possible
Это прекрасно!
Спасибо :)
Hi Bas, Thank you for amazing videos. I need one urgent help. I want 3 card visuals in my report. First will show highest selling brand along with sales amount, second will give second highest selling Brand along with sales amount and similarly for third. I tried a lot but no luck. Can you pls guide me here with measure or anything. Thank you!
In the same vein of difficulty selecting things, if I have a measure that is limited by a date range, and I want to show all the dates in the range even if the measure has a value of zero, how do I go about that? All my attempts end up with "all dates" in my date table
Found a solution but I don't like it too much
measurename =
vars and stuff
var firstdate =
var lastdate =
return
measure calculation
+ if (min(calendar date) = firstdate, 0, BLANK() )
Bas, how to count RT % of sales - total qty
Scheduled refresh is slow when compare to manual refresh
You guys know anyother way to do automatically using other apps
Like power shell or python
Sir i cant find the filter type option in my sliced chart or other visual reports. Please help
You've used both ALLSELECTED and REMOVEFILTERS. Are they interchangeable?
Hi Roberto, thx for watching!
They are not the same ->
When used inside of the calculate function both ALL and REMOVEFILTERS are the same. ALLSELECTED would remove the filters from the visual, but not outside filters (from a slicer, filter panel or other visual).
In the rankx function I use the all function to return all the rows in a table, or all the values in a column, ignoring filters, and allselected if you want to keep filters from outside the visual. The rankx function then iterates over that table. REMOVEFILTERS can only be used to clear filters but not to return a table like all and allselected, so therefore wouldnt be an option here.
@@HowtoPowerBI please help, how to count % of Running total of sales - total qty ??
on distinct value can we use
Hello
Top👍 de "France"
Anyway to get a chart to auto drill down when only one item is left in the filter?
I don't think that's possible 🙄
@@HowtoPowerBI please help, how to count % of running total - total qty
Bas, great!! Cudo's
thank you Frank 😊
First like 👍
Yey 😁🥳
@@HowtoPowerBI Thanks for sharing this video 👌 It will resolve all the issues faced in TOPN 🙂
RankByAmountFails = RANKX(ALLSELECTED(DimCustomer),SUM(DimCustomer[Amount]))
What is wrong with this one? It is still giving me always 1 for ranking although the amounts are different.
E.g. the following is working as supposed ...
RankyByBirthdayWorking = RANKX(ALLSELECTED(DimCustomer),FIRSTDATE(DimCustomer[BirthDate]))
Edit: Ok, I see using Calculate or using a Mesure for Sum is the way to go again here ....
Edit2: Ok, I should first watch videos till the end :D