It's possible to do a foreach on power bi ?

[AlexGallet01]

Hello,

I have a problem. I need to do some sort of foreach on power bi desktop because I have a number of commands with different lines and I want to take the last line number of this command but if i go through a function to take the last Line from maxvalue, I just have one result for all the number of commands. So I think I am forced to use some kind of foreach but I do not know if it is possible to do this function on power bi desktop or simular.

[azarel]

Yes, there is a foreach function in PowerBI. Don't know why, but it's labelled as SUMX, as a programmer I don't get this logic, it doesn't sum it evals an expression for each line in the designated table. Anyway, to use it as a foreach parse the target table as the first argument and the operation you want to do as the second. In my case, I'm multiplying the amounts in a value column by a percentile multiplied by the days since the amount was paid to get a simple interest calculation.

As a more concrete example; if you have a booking table where you store room-bookings for a hotel, the date for the booking, the number of guests staying, and the price per person. You can write a measure to calculate the total price by doing SUMX( 'Bookings', 'Bookings'[Guests] * 'Bookings'[Price] ). The cool part about this is that it actually puts a concrete value in per line item that you can tabulate and accurately summarise if you wanted to do a matrix table so you could have daily totals and the like. Before knowing about SUMX I was doing stupid things with MAX which would get accurate line item calculations but could never be summarised, fine for some things but not for others.

[AlexGallet01 · Accepted solution]

Hello, 

It's good I've done otherwise. I create a group by command with the maximum value of the row.

[AlexGallet01]

Thank's but i can this result in a column in my table no in schéma

[Greg_Deckler]

You may have to use a measure and not a column. And you might want to check out MAXX.

https://msdn.microsoft.com/en-us/library/ee634576.aspx

All of the "X" functions (SUMX, COUNTX, etc.) are kind of foreach like in that they evaluate something for each row of a table. With the right filters applied, you should be able to achieve the desired result.

Also, you may not be thinking enough Power BI here. If you simply created a table visualization and put what you want to summarize by and then the MAX of your other value, you would have what you are looking for.

If you truly want a table in the model, then use SUMMARIZE along with MAX.

[Anonymous]

It would be easier to come up with something that might help you if you post a sample of the data you're working with, and what you're trying to get out of it.

The concept of "foreach", such as in a language like C#, is procedural in nature, whereas both M and DAX are designed to work on sets in a functional manner (i.e. you describe the result, and not the steps it takes to get to the result).

[AlexGallet01]

I have a datatable like this

And for one THKTNODOS i have differents value of THKNNOLIGN. And i want the max value of THKNNOLIGN by THKTNODOS.

[Anonymous]

There is already a built-in Maximum aggregate for any visualization type that will do exactly what you're looking for. Just make sure that your "THKNNOLIGN" column is a numeric type.

[Greg_Deckler]

M code or DAX?

[AlexGallet01]

DAX