topic Re: DAX to find last non blank column in a given row in DAX Commands and Tips

DAX to find last non blank column in a given row

ilyav — Mon, 12 Dec 2022 14:19:07 GMT

Hi Everyone,

I have a DAX question, my use case is that I have multiple rows and unknown number of columns (could be between 5-15), the data is generated by a Python script with a single input and generates an ouput with varying number of columns.

I need to find the last column in a given row that has value in it from a conditional column.

Here is an example of the data as reference and the desired output 'Total' which takes the last non blank value from the last 'Child' column that has values.

Appreciate any help with this!

Thanks,

Ilya

Re: DAX to find last non blank column in a given row

Manoj_Nair — Mon, 12 Dec 2022 19:41:05 GMT

ilyav - pls try this DAX, we used Coalesce.

Let me know if this resolve your issues then please mark as Solution Accepted. Many Thanks

Re: DAX to find last non blank column in a given row

ilyav — Tue, 13 Dec 2022 06:51:53 GMT

Manoj_Nair Thank you very much for the reply!

I guess that will solve the static case if I have a fixed number of columns. What will happen to the code if I have an additional column or less 2 columns? As I wrote, the number of "Childs" is dynamic and I need to find a way to deal with it dynamically in DAX (or M code) 🙂

BR,

Ilya