Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
luciancostin
Regular Visitor

Self-join table dynamically

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])

 

?

 

 

3 REPLIES 3
luciancostin
Regular Visitor

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
Super User
Super User

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...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.