@@jonimatix The basic idea is to use a table function like FILTER / CALCULATETABLE / SUMMARIZE etc. to filter the data source and to compute the required the value with an iterator like SUMX / MAXX / COUNTX etc.
Hi Ruth! In a scenario, a product and category both are in same table. And I want to filter product by each category separately. i.e. products split into two slicers named bikes products and components products. When I filter each product, it should add to crosstab. Can you please help me how to achieve this?
Hi, i've tried that, and that works for unique id's... if instead of having an order id, you have a sales person name with multiple sales amounts, and you want to see what is his rank (of total sales), against the other sales people.. what needs to be changed in the code? i've tried the below, but it did not gave the correct output sales person rank = RANKX( ALLSELECTED([Sales person name]), CALCULATE(sum([Sales])) ,,ASC ) and instead of having 1 to n output, i got 1, 1, 2, 3, 1... Could you help? much appreciated! Diogo
Hi Ruth - another clarity on difference between scalar & table functions! Reminds me of the song, "I Can See Clearly Now" (original by Johnny Nash) :-) Question though. At the 6:53 mark, the RANKX measure result showed sales ranked accordingly. However, I would have expected sales of same values to be ranked equally. For example, I would have expected ranks 1 & 2 to be 1 & 1 and then 3, etc. Actually, the only ranking that I saw clearly was rank 12. In other cases, like sales figure 10.540 - first 2 ranked the same but the 3rd instanced ranked 5! Could it be a case rounding in the background of the calculation? Just curious. Otherwise, I see clearly now when to use each (confused me many times)
Give me the perfect data source set (which is useful for all your DAX videos). I saw the video getting the NorthWind data set but, I couldn't understand the ETL operations on it, and also it differs from other Dax videos data like columns in each table. So, Please guide me
Thanks for sharing this knowledge Ruth !! I recently came across a situation where a data set is having 0 values for few of the rows and I used RANKX function to helped me to get the top 5 and bottom 5 because the requirement was to see those numbers from ranking 1-5 for top and as well bottom 1-5. I used the visual level filter for top 5 and it was really working fine but when i do visual level filter for the bottom 5 it was only returning the zeroes in the visualization and customer don't wanted to see zeroes. Can you please elaborate more on the BOTTOM calculation or what is the best approach apart from visual level filter where we have null values or 0 values in the dataset !! Thanks again!!
I have a survey where we asked in the rank of same products as 5 questions - Rank 1 - A/B/C/D Rank 2 - A/B/C/D Rank 3 - A/B/C/D Rank 4 - A/B/C/D Any easy way to calculate these options A,B,C,D on the basis of rank weight?
Hi Ruth, thanks as always for this video. I have a question about RankX which is killing me! If the measure that I want to rank is itself calculated across multiple tables (using USERELATIONSHIP), what would I set in the first parameter of RANKX? Do I need to do a manual join on the various tables and then summarize it? I can't seem to understand the syntax that I need :-(
Hello Ruth! Nice video. I have a problem to solve and i dont know an easy way with DAX: I have a date field, a product category, product and sales, how can i obtain the top 10 or 20 or X from the sales but from each category? For example, from march third 2020, these are the top 10 product sales in the cleaning products...
Has anyone tried using TOPN to return an Nth value, for example 3rd highest, TOPN( 3, Table, Table [ Sales] ) , then wrap in in MIN ( TOPN( 3, Table, Table [ Sales] ),[Sales]) this doesn't seem to work, but I feel it should. Any suggestions?
![Felix "Unfair" | [Stray Kids : SKZ-PLAYER]](http://i.ytimg.com/vi/Oswujxm2Ag0/mqdefault.jpg)
Thanks Ruth, you do not know how much you are helping me! Every Friday I am waiting for your Friday Dax video to learn something new and fun!
Glad to hear!! :)
Love the way you explained these concepts. I’m preparing for the PL300. Liked and subbed.
Welcome and good luck!
@@CurbalEN Quick update! I passed the exam this morning!!! 😃 Hope to keep consuming your content to learn from it!
@PianoandCoffee Congrats!!! 🥳🥳🥳
Thanks Ruth.. Really appreciate for your efforts to provide such information to audience. I am big fan of yours. 😊
Thanks!
Thanks Ruth, Cleared my confusion
Awesome that was super clear and helpful.
Have a great day :)
Well explained Ruth! TOPN+SUMMERIZE+ITERATOR ....one of the strongest combos in DAX I know. Have a nice weekend!
Same to you Victor! Happy Friday:)
Can you give an example of TOPN+SUMMERIZE+ITERATOR ? Really interested to know!
@@jonimatix The basic idea is to use a table function like FILTER / CALCULATETABLE / SUMMARIZE etc. to filter the data source and to compute the required the value with an iterator like SUMX / MAXX / COUNTX etc.
Very clear to me. Many thanks!
Very pedagogical as always Ruth, thanks...
Happy Friday Ruth, pretty clear explanation. :P
Happy Friday!!
Thanks for the video, it's just saved me.
You really explained it so beautifully! Thanks a lot!
Thanks ,making a great video for us .this topic had before confision ,now is good.
Then I did my job right. Happy Friday!
Thanks ®u|-# i learnt all dax functions from your channel your doing great work 👍
🥳
Hi Great video, do you know of anyway i can use a table created inside a measure , in a table visual
Once again, clear cut explanation and it helps a lot compared to reading from Dax guide :)
Thanks for the feedback and happy Friday?
Hi Ruth. Good explanation. Thank you!
Happy Friday:)
Thank you! Very easy to understand!
Perfect!
I have topn returning last two rows of a existing table with four columns, into a Variable, how would i refer individual colimn in that topn variable
Good. But, I cannot see what you are dragging from. That is when I am lost. (around 6:45, when you are talking about RANK)
How do you rank clients based on a measure not a calculated column?
I have do I get the rankings to not re-rank during drill through from District to a Franchise?
Thank you for such great content!!!!
Has anyone ever told you that you look and sound like Skylar from Good Will Hunting?
Hi Ruth! In a scenario, a product and category both are in same table. And I want to filter product by each category separately. i.e. products split into two slicers named bikes products and components products. When I filter each product, it should add to crosstab. Can you please help me how to achieve this?
Nice video... but what if I want to show a range Eg Top 11-40 clients?
Hi,
i've tried that, and that works for unique id's...
if instead of having an order id, you have a sales person name with multiple sales amounts, and you want to see what is his rank (of total sales), against the other sales people.. what needs to be changed in the code?
i've tried the below, but it did not gave the correct output
sales person rank =
RANKX(
ALLSELECTED([Sales person name]),
CALCULATE(sum([Sales]))
,,ASC
)
and instead of having 1 to n output, i got 1, 1, 2, 3, 1...
Could you help?
much appreciated!
Diogo
You are awesome!!
❤️
Hi Ruth - another clarity on difference between scalar & table functions! Reminds me of the song, "I Can See Clearly Now" (original by Johnny Nash) :-)
Question though. At the 6:53 mark, the RANKX measure result showed sales ranked accordingly. However, I would have expected sales of same values to be ranked equally. For example, I would have expected ranks 1 & 2 to be 1 & 1 and then 3, etc. Actually, the only ranking that I saw clearly was rank 12. In other cases, like sales figure 10.540 - first 2 ranked the same but the 3rd instanced ranked 5! Could it be a case rounding in the background of the calculation? Just curious. Otherwise, I see clearly now when to use each (confused me many times)
That is possible if you use Dense as an argument in Rankx Function
Awesome!!
Thank you for the video Ruth! quick question: why do we use all with RANKX but not with TOPN?
Give me the perfect data source set (which is useful for all your DAX videos). I saw the video getting the NorthWind data set but, I couldn't understand the ETL operations on it, and also it differs from other Dax videos data like columns in each table.
So, Please guide me
You are the best
You too
Thanks for sharing this knowledge Ruth !! I recently came across a situation where a data set is having 0 values for few of the rows and I used RANKX function to helped me to get the top 5 and bottom 5 because the requirement was to see those numbers from ranking 1-5 for top and as well bottom 1-5. I used the visual level filter for top 5 and it was really working fine but when i do visual level filter for the bottom 5 it was only returning the zeroes in the visualization and customer don't wanted to see zeroes. Can you please elaborate more on the BOTTOM calculation or what is the best approach apart from visual level filter where we have null values or 0 values in the dataset !! Thanks again!!
Ruth, in my rankx tables rank is duplicated.. can you please help
How to get TOPN orders with ties, 10 orders with different amounts (e.g. in your resul you only have top 6 not top 10 orders in fact)?
I have a survey where we asked in the rank of same products as 5 questions -
Rank 1 - A/B/C/D
Rank 2 - A/B/C/D
Rank 3 - A/B/C/D
Rank 4 - A/B/C/D
Any easy way to calculate these options A,B,C,D on the basis of rank weight?
Please help me analysis service power bi, how to refresh data automatic
Thanks Ruth
You welcome and happy friday!
Thks for video enjoy ur weekend
You too!
Kristallklart efter att ha sett din video.
Hi Ruth, thanks as always for this video. I have a question about RankX which is killing me! If the measure that I want to rank is itself calculated across multiple tables (using USERELATIONSHIP), what would I set in the first parameter of RANKX? Do I need to do a manual join on the various tables and then summarize it? I can't seem to understand the syntax that I need :-(
Sounds like a good question for the power bi community. Make sure to give details , as how your model is laid out to get correct answers!
Nice! See you monday👍🥂
Another battle planned for Monday;)
Hello Ruth! Nice video. I have a problem to solve and i dont know an easy way with DAX: I have a date field, a product category, product and sales, how can i obtain the top 10 or 20 or X from the sales but from each category? For example, from march third 2020, these are the top 10 product sales in the cleaning products...
Have you tried asking in the power bi community?
hi ruth thanks for the video!
now related vs relatedtable please ? :)
I have one on that already!
@@CurbalEN yess saw it brilliant thanks alot !!
Happy Friday!
thanks 🙏
😊
perfect
Has anyone tried using TOPN to return an Nth value, for example 3rd highest,
TOPN( 3, Table, Table [ Sales] ) , then wrap in in MIN ( TOPN( 3, Table, Table [ Sales] ),[Sales]) this doesn't seem to work, but I feel it should. Any suggestions?
How about topn/ bottom n customers excluding 0 sale value
how to get bottom 10 ranks sales
very clear Thanks a lot :)
Ruth looks very intimidating in the thumbnail. She got my attention.
😂
I will change it for a friendlier face next time!
u look beautiful