DAX Fridays! #93: MAXX | Fill down with DAX and Power Query

[@navaidmansoor7945]

I searched for about a week before i stumbled upon this video which solved my problem to the letter. Tnx a bundle

[@mehdihammadi6145]

Thank you Ruth, this is a very interesting use of the MAXX function

[@CurbalEN]

Isn’t it? And extremely useful :) Happy Friday! /Ruth

[@Victor-ol1lo]

Super function ... and as I learnt recently can also deliver text values within measures.... Thanks for sharing and have a nice weekend !!!

[@CurbalEN]

Same to you :D /Ruth

[@sgk1966]

Thank you. I was doing it in power query. But never knew we could do it in DAX and it is simple. Thank you once again for sharing knowledge.

[@CurbalEN]

To be fair, i didnt know either until I run into a case where I had to do it in DAX and I thought it would be useful to others too :) Happy Friday ! /Ruth

[@MrPapeBoubacar]

fantastic! A couple of weeks ago, I was facing a similar data transformation issue. I had to export a ledger to Excel and with a complex combination of countif and offset , I was able to fix the problem. This looks way easier. I will give it a try. Thanks!!

[@CurbalEN]

It definitely is :) Happy Friday!! /Ruth

[@tonyandlisab]

Love it - just what I was after. Thanks Curbal

[@CurbalEN]

Music to my ears :)

[@nicoymartina]

Thank you, Ruth!

[@tanababa]

Thank you Ruth. Great example. However, I I don't understand why it actually works. In time stamp 05:38, you describe the equal expression which seems strange to me. Wouldn't it compare the current row value of product ID to the whole column each time? How does the Dax "knows" to check only for the same product ID?

[@CurbalEN]

I explained that poorly... I saw it when I was editing, but didn’t have time to redo it. Have to do it again... /Ruth

[@tanababa]

Looking forward to it :-)

[@CurbalEN]

I will do a Quick update on Tuesday!

[@tonyandlisab]

Hi Ruth, just trying this and as a measure it works but I'm failing miserably when trying it on a calculated column (returns a blank column). Is there something obvious I forgot to do? ❤

[@AravindanM93]

Hi Ruth we have an issue, we have a live connection connected to. When the user opens the report the user should see by default the data of today's and the should be able move backward also. We have used between slicer. The problem is we aren't able to set the slicer to current date.

[@CurbalEN]

You should be able to do it, can you post the question in the power bi community? Thanks! /Ruth

[@andrewrender]

What do you do if you don't have a 'prodno, column to reference? Ie just a unique list of dates and then old lines missing on the country column?

[@andrecavasotero6234]

Great. I did not know this utility of the MAXX function. Thanks for sharing.

[@CurbalEN]

You welcome and happy Friday! :) /Ruth

[@sobsawats.9109]

Thank you. Good trick. I have another idea to solve this problem by using column like ColumnFillDown = CALCULATE(MAX(tbl1[Country]),FILTER(tbl1,tbl1[ProdNo]=EARLIER(tbl1[ProdNo]))) Any disadvantage on this? performance? Please kindly comment. Thank you.

[@CurbalEN]

Try them both in DAX studio and let us know ;) /Ruth

[@sobsawats.9109]

Thanks, I never used DAX studio. will try.

[@CurbalEN]

Oh sorry! Maybe this video will help answer your question a bit: m.kzbin.info/www/bejne/iZ67fZp7fb2GfcU But keep in mind that performance is highly dependent on how your model and data looks like. That is why you need to test it in your own model. /Ruth

[@sobsawats.9109]

Curbal Thanks again. I’ve got your point.

[@maxivy3299]

This formula works with numbers which is even better

[@sahuanalogchannel]

Hi Ma'am, what happened if we have two country value for same product id. Will this work or not?

[@rassten]

That was a really good feature, I would normally have used Excel, but you can not do it with big data. Now that Power week is done, what should we do on Tuesday and Thursday next week, you spoil us???

[@CurbalEN]

I am posting my new channel intro on Tuesday, so you will get 4 videos next week! So you detox slowly 😂😂😂 Happy Friday! /Ruth

[@michaelm195ooo]

Hi, how can I add another condition if there is Product # 7040357 in Germany and Italy?

¡Tremendo!

[@CurbalEN]

Gracias! /Ruth

[@selenevera8834]

Thank you. This is amazing ; )

[@CurbalEN]

And thanks for the feedback!! /Ruth

[@pratikfutane8131]

Fantastic

[@CurbalEN]

Thanks! /Ruth

[@sbn4862]

Evry 10 or 15 days we change currensy, I have one table with currency and second one is Datetable. PowerQuery combines and fills down very easily. But DAX can't do it no MAXX no Summrise no Calculate and calculattable can't help and Varables too. I must do it in calculating table not measure. Can you help me Please

[@Unbox747]

Thanks this solved a big problem for me. Who would have know you can apply a MAX function to a column of text. 😮

[@CurbalEN]

Right? 🤷‍♀️

[@shashikanthbasuthkar95]

That's really good How to calculate condition column from multiple tables Example: Sum(case when (Table1.Column1="D" AND Table2.Column1="20" AND Table3.Column="New" THEN (Table4.SalesAmount)*(Table4.Parcent) * -1 when (Table1.Column1="R" AND Table2.Column1="20" AND Table3.Column="Ren" THEN (Table4.SalesAmount)*(Table4.Parcent) Please make a video for this colculation my Humble request

[@CurbalEN]

I am sorry to say this, but it will be faster to ask the Power bi Community! I have other videos scheduled. /Ruth

[@shashikanthbasuthkar95]

I already posted in community But I did not get answers Please give me an answer as a comment it's my Humble request

[@CurbalEN]

Did you post sample Data so people can easily help you? /Ruth

[@shashikanthbasuthkar95]

please Find the XL link: drive.google.com/file/d/17IU7fHJkAir2XQJP_UZvF0wVoHlMLOy3/view?usp=sharing

[@shashikanthbasuthkar95]

Case when policy_type.policy_type =D and transaction.trn_type=10 and transaction.trn_rid is not null then fact.amount When when policy_type.policy_type =R and transaction.trn_type=10 and transaction.trn_rid is null then fact.amount * -1

[@michaelm195ooo]

Hi, how can I add another condition if there is Product # 7040357 in Germany and Italy?