Self-join table dynamically

[luciancostin]

Hi Community,

Can i dynamically join a table with itself dynamically? e.g. for all the distinct values of one column, join the table with itself on a few other columns?

Or , otherwise, how can i , from the same values table (values of different meanings), multiply values from different rows? 

E.g.: For product A, 10 * 14 from a table similar to the below

Product, KPI, month, value

A, 1, 1, 10

A, 1, 2, 11

A, 2, 1, 12

A, 2, 2, 13

A, 3, 1, 14

Something like: Multiply ( value [where kpi=1], with value [from table, where product=current product, month=current month, kpi = 3])

?

[luciancostin]

Hello community,

I'm new to Power BI, and i've been going through the forums here, but i didn't understand how to solve this problem:

I have a table with values for different kpis, like:

Product, KPI, month, value

A, 1, 1, 10

A, 1, 2, 11

A, 1, 3, 12

A, 2, 1, 20

A, 2, 2, 21

A, 2, 3, 22

B, 1, 1, 30

B, 1, 2, 31

.....

In our previous approaches, with other tools, we used to programatically join the table with itself, for each KPI, on (product and month), resulting in something like:

Product, month, value(kpi1), value(kpi2)

A, 1, 10, 20

A, 2, 11, 21

A, 3, 12, 32

B, 1, 30, 

B, 2, 31,

I'm still stuck into this approach philosophy, and haven't been able to achieve this in Power BI. 

My actual purpose is to compute operations between different KPIs of the same product, during the same month, as for example KPI 1 x KPI 2:

Product, month , value (KPI 1 x KPI 2)

A, 1, 200

A, 2, 231

A, 3, 384

B, 1, 0

B, 2, 0

I've kept trying to add a new column with the corresponding values, both in the data view or in the query step, without luck. 

On the other hand, what would the best approach in Power BI be, to do such an append for all KPIs dynamically, without knowing their values?

Sorry if i missed any good help topics, if anyone can at least point me in the right direction?

Thank you for any hints,

[Greg_Deckler]

Sure, you would just create a custom column and use EARLIER. Here is a blog article on EARLIER. See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[luciancostin]

Thank you, Greg,

Here is what i ended up with, to get a single other row computation:

computed = sumx(filter('table';and(and(and('table'[product]=earlier('table'[product]);'table'[kpi]=1);'table'[state]=earlier('table'[state]));'table'[month]=earlier('table'[month]))); 'table'[value]) * sumx(filter('table';and(and(and('table'[product]=earlier('table'[product]);'table'[kpi]=3);'table'[state]=earlier('table'[state]));'table'[month]=earlier('table'[month]))); 'table'[value])

Is this the way you would have seen it too? i find it quite complex, to be honest, is there any other easier, more straightforward way?