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.
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
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 :)
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, 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!
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.
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 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?
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, 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 :)
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
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
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!
Where is your cape Curbal? My hero! :) Thank you so much!!!
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:)
Fantastic Video, Ruth!!!!
Have been struggling with this very topic! Thanks
Happy to learn from you ,your teaching style is easy and fun
Great to hear :)
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?
This video is helpful! By the way the pbi file for this session is not available, if you don't mind to upload it..
Hey, Happy #200 anniversary. Thanks a lot and keep it up.
Great video,
Will you please make a video to calculate a running total for #trips from least to greatest? Thanks
Thank you! I already have a very important use case for this
Perfect timing then!
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
So helpful, thank you so much. Can't wait to learn from more videos!
Hi Ruth,nice and soft way of explanation
Great :)
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 :)
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!!
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, 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!
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!
Very helpful, thank-you!
Nice explanation to a fairly common mistake! Thanks!
You welcome!
Again, Great Video. Wondering how this can be modified to capture the max of a moving average. Might be an interesting video.
Noted :)
Superb @Ruth. Thanks a lot for creating and sharing this video.. It helped a lot.. 😀👍
Wonderful:)
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 !!!
Great video! Thanks!
🥳🥳
Excellent!!
Ole!!
Congrats on #200, Ruth! 🎊 How many more to go now?
really useful
Hello Ruth, I have a question. Can we select few station and re-calculate the max? Thank you. 😃
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?
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, 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.
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
🎉🎉
Thanks a lot
My pleasure
Life-saver
🎉
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 #])
Hello dear power bi files are not downloading.
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
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.
My concern, how to get min and max of weekly values?
What if the measure uses data from two tables?
It works, untill you put a slicer on the table. You then want the result based on the sliced dataset.