Hi Ruth! Thank you for your incredible videos, I really love them!!! Could you make a video teaching how to get for example the name of the best selling product per period? Like in the case of the bicycle, instead of bringing the maximum value, return the name of the station that had more trips. Kind regards, Ana.
Great piece! Another example to solve the same situation. As of now, I have been using the following: VAR AllValues = CALCULATETABLE( ADDCOLUMNS ( SUMMARIZE ( 'Investments', 'Investments'[Date] ), "@Sales", [BTC TOTAL] ), ALLSELECTED () ) VAR MaxValue = MAXX ( AllValues, [@Sales] ) RETURN MaxValue I guess now it's a matter of testing which one is faster :P
@@CurbalEN I guess I will do because I tested just now with a sample of data I have and the difference was 0.5 ms 😁. The difference however was in the usage of the Formula Engine vs Storage Engine. According to DAX Studio, we should get measures that ideally spend the most time in the Storage Engine. Will update once I get some results :)
Hi Ruth, how are you? Could you please refresh my mind and help us on how to return the NAME in a card once the MAX number was found? So, once 72799 is the max value, how can I put name "21st & Speedway @PCL" in a simple card that just allows measures. Thanks in advance!
Hi Ruth, This is such an incredible video with all the details explained. I did use the exact same thing in my Organisation and was super happy to get the correct result. A quick question, instead of calculating MAX if we want to use SUM of the Total for a measure, is it doable? Thanks! Akshay
Hi Ruth, I am trying to calculate min of a measure for a group. So, I have a measure to calculate average price and I want to find the lowest avg. price in a month for an item using this measure. How do I do this?
Happy Friday Ruth, MAXX, SUMXX, RANKX things always confused me at the beginning of learning DAX. I should watch this video earlier before I made tons of mistakes. Thanks again for your post.
Superb video! Is it possibile to create a column reporting the MAX value in correspondence to the relative row ("21st & Speedway @PCL" in this case) and assign 0 to the other rows?
Hi Ruth! Any idea on how can we get the MAX of 2 different measures ? Something like having the max of multiple columns but with measures. Many thanks for your great videos !!!
Hi, I have a question or rather a suggestion. When you are trying to demonstrate a technique, do you always have to use a mega sized dataset? Why not use a simple dataset with just enough number of fields and rows, so the reader can visually observe how the technique is working. For your consideration please.
I have a date column and each date also have sale data, so i have totally two columns, one is Date, 2nd is sale value. the requirement is pick the max date and min date show the total sale? how do i achieve this
Great video. Been learning a lot from you. Thanks. I was thinking of applying this to a matrix that have multiple levels. How can we get the max dynamically? example:Level 1= bike station, Level 2 = Lets say passenger gender. When the table is collapsed to Level 1, we get the max like you have but when we expand to level 2, we get the max of station by gender. I have a matrix with mutiple levels and I am struggling to find a solution.
This is great info! Any ideas how I can get this to work when the measure I'm trying to get the maxx of is conditionally calculated. Basically, I have a variance that is calculated based on the first and last categories selected. The graph then has multiple categories per week along the x axis and total value sold in the Y. The measure I'm trying to get the max of calculates the difference between the first and last selected category. Trying the following with or without the ALL just displays the total value for each week: maxx(ALL(Summary),[Variance Over Selection]) I'm using the following to get which categories to compare: Selection = VAR _Count = COUNTROWS(VALUES('S&OP Archive'[Archive Date])) VAR _Concat = CONCATENATEX(VALUES('S&OP Archive'[Archive Date]),Format([Archive Date],"yyyyMMdd"),"|") RETURN _Concat and the following to get the Start data: Comparison Revenue Start = VAR sel = PATHITEM( [Selection], 1 ) RETURN CALCULATE([Forecast Revenue],Format('S&OP Archive'[Archive Date],"yyyyMMdd") = sel) and the following to get end data: Comparison Revenue End = VAR sel = PATHITEM( [Selection], PATHLENGTH([Selection]) ) RETURN CALCULATE([Forecast Revenue],Format('S&OP Archive'[Archive Date],"yyyyMMdd") = sel) With the variance calculated as: Variance Over Selection = [Comparison Revenue End]-[Comparison Revenue Start]
Hi,I am facing same issue I have to calculate max of count order against month column ,but everytime it gives max at total but values got filter by month in table please help
Hi, that's very helpful! I was also wondering if you can Calculate the MAX of a filtered measure via slicer. I looked for an answer how to do it, but I didn't find anything that I could understand and use
Hi, probably a little bit late lol, but i had the same question. I did MAXX(ALLSELECTED('Table'), [Measure]) and it worked - so i used ALLSELECTED() instead of ALL(). Hope i could help you :)
Ruth, you are a star. Thank you, thank you, thank you.
😊😊😊
Great video, it helped me out a ton. You are a good teacher, keep it up!
Hi Ruth! Thank you for your incredible videos, I really love them!!! Could you make a video teaching how to get for example the name of the best selling product per period? Like in the case of the bicycle, instead of bringing the maximum value, return the name of the station that had more trips. Kind regards, Ana.
Mmmm second person asking, I will add it to my list:)
Great piece! Another example to solve the same situation. As of now, I have been using the following:
VAR AllValues =
CALCULATETABLE(
ADDCOLUMNS (
SUMMARIZE ( 'Investments', 'Investments'[Date] ),
"@Sales", [BTC TOTAL]
),
ALLSELECTED ()
)
VAR MaxValue = MAXX ( AllValues, [@Sales] )
RETURN MaxValue
I guess now it's a matter of testing which one is faster :P
If you connect with Google Big query public datasets you can test it 😁
@@CurbalEN I guess I will do because I tested just now with a sample of data I have and the difference was 0.5 ms 😁. The difference however was in the usage of the Formula Engine vs Storage Engine. According to DAX Studio, we should get measures that ideally spend the most time in the Storage Engine. Will update once I get some results :)
Looking forward the results!!
Happy to learn from you ,your teaching style is easy and fun
Great to hear :)
Fantastic Video, Ruth!!!!
Have been struggling with this very topic! Thanks
Thanks for the video, I was struggling with this problem
Thanks Ruth!
But how can i calculate the max for each bike station and with the sum in the end off all the max by each bike station?
Thank you! I already have a very important use case for this
Perfect timing then!
Hi Ruth, how are you? Could you please refresh my mind and help us on how to return the NAME in a card once the MAX number was found? So, once 72799 is the max value, how can I put name "21st & Speedway @PCL" in a simple card that just allows measures. Thanks in advance!
Hey, Happy #200 anniversary. Thanks a lot and keep it up.
Hi Ruth,
This is such an incredible video with all the details explained. I did use the exact same thing in my Organisation and was super happy to get the correct result. A quick question, instead of calculating MAX if we want to use SUM of the Total for a measure, is it doable?
Thanks!
Akshay
Superb video technic, easy to see what you are doing, even on my phone and without my reading glasses :) Its very cool, and informative as usual
I have been putting more effort into the editing, glad it shows! And happy friday :)
Hi Ruth, I am trying to calculate min of a measure for a group. So, I have a measure to calculate average price and I want to find the lowest avg. price in a month for an item using this measure. How do I do this?
Happy Friday Ruth, MAXX, SUMXX, RANKX things always confused me at the beginning of learning DAX. I should watch this video earlier before I made tons of mistakes. Thanks again for your post.
I should have done that too!! Oh wait... that is not possible right? 😂😂😂
Happy Friday Danny!
Again, Great Video. Wondering how this can be modified to capture the max of a moving average. Might be an interesting video.
Noted :)
This video is helpful! By the way the pbi file for this session is not available, if you don't mind to upload it..
Great video,
Will you please make a video to calculate a running total for #trips from least to greatest? Thanks
So helpful, thank you so much. Can't wait to learn from more videos!
Excellent!!
Ole!!
Hi Ruth,nice and soft way of explanation
Great :)
really useful
Superb video! Is it possibile to create a column reporting the MAX value in correspondence to the relative row ("21st & Speedway @PCL" in this case) and assign 0 to the other rows?
Nice explanation to a fairly common mistake! Thanks!
You welcome!
Hi Ruth!
Any idea on how can we get the MAX of 2 different measures ?
Something like having the max of multiple columns but with measures.
Many thanks for your great videos !!!
Hi, I have a question or rather a suggestion. When you are trying to demonstrate a technique, do you always have to use a mega sized dataset? Why not use a simple dataset with just enough number of fields and rows, so the reader can visually observe how the technique is working. For your consideration please.
I do sometimes, it depends on if I think it is needed or not. I dont always get it right though.
Thanks for the feedback :)
Hi Team, I tried this today but not working for me. Im getting default value as "1". DAX - NCD check = MAXX(Master,[NCD #])
Very helpful, thank-you!
Superb @Ruth. Thanks a lot for creating and sharing this video.. It helped a lot.. 😀👍
Wonderful:)
Congrats on #200, Ruth! 🎊 How many more to go now?
I have a date column and each date also have sale data, so i have totally two columns, one is Date, 2nd is sale value. the requirement is pick the max date and min date show the total sale? how do i achieve this
Great video. Been learning a lot from you. Thanks. I was thinking of applying this to a matrix that have multiple levels. How can we get the max dynamically? example:Level 1= bike station, Level 2 = Lets say passenger gender. When the table is collapsed to Level 1, we get the max like you have but when we expand to level 2, we get the max of station by gender. I have a matrix with mutiple levels and I am struggling to find a solution.
Hello Ruth, I have a question. Can we select few station and re-calculate the max? Thank you. 😃
My concern, how to get min and max of weekly values?
Thanks a lot
My pleasure
This is great info!
Any ideas how I can get this to work when the measure I'm trying to get the maxx of is conditionally calculated. Basically, I have a variance that is calculated based on the first and last categories selected. The graph then has multiple categories per week along the x axis and total value sold in the Y. The measure I'm trying to get the max of calculates the difference between the first and last selected category. Trying the following with or without the ALL just displays the total value for each week:
maxx(ALL(Summary),[Variance Over Selection])
I'm using the following to get which categories to compare:
Selection =
VAR _Count = COUNTROWS(VALUES('S&OP Archive'[Archive Date]))
VAR _Concat = CONCATENATEX(VALUES('S&OP Archive'[Archive Date]),Format([Archive Date],"yyyyMMdd"),"|")
RETURN _Concat
and the following to get the Start data:
Comparison Revenue Start =
VAR sel = PATHITEM( [Selection], 1 )
RETURN CALCULATE([Forecast Revenue],Format('S&OP Archive'[Archive Date],"yyyyMMdd") = sel)
and the following to get end data:
Comparison Revenue End =
VAR sel = PATHITEM( [Selection], PATHLENGTH([Selection]) )
RETURN CALCULATE([Forecast Revenue],Format('S&OP Archive'[Archive Date],"yyyyMMdd") = sel)
With the variance calculated as:
Variance Over Selection = [Comparison Revenue End]-[Comparison Revenue Start]
Great video! Thanks!
🥳🥳
Hello dear power bi files are not downloading.
Amazing! I think I will need to use it soon! Buen fin de semana campeona guru de BI
Igualmente Bruno! 😂
@@CurbalEN Now, I have just use it again! Thanks again! Graciaaaas
🎉🎉
Hi,I am facing same issue I have to calculate max of count order against month column ,but everytime it gives max at total but values got filter by month in table please help
Did you ever find a solution? I have the same probelm I believe.
Life-saver
🎉
It works, untill you put a slicer on the table. You then want the result based on the sliced dataset.
Hi, that's very helpful! I was also wondering if you can Calculate the MAX of a filtered measure via slicer. I looked for an answer how to do it, but I didn't find anything that I could understand and use
Hi, probably a little bit late lol, but i had the same question. I did MAXX(ALLSELECTED('Table'), [Measure]) and it worked - so i used ALLSELECTED() instead of ALL(). Hope i could help you :)
@@erikbossow Hey, thanks! :) I can't remember now how I solved it, but I also found a solution.
What if the measure uses data from two tables?