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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Is it possible to code this in DAX?

Hello World!

Is it possible to code this in DAX - im having a hard time doing so and reltively new to DAX!

 

=(X/Y)^(1/((P-Q)*100))-1

 

Where X and Y are related in the same column - P and Q are also related in the same column.

However X and Y are not in the same column as P and Q

 

X is in the same row as P, Y is in the same row as Q 

However X and P are not in the same row as Y and Q

 

XP
YQ

 

Finding it hard to relate to a specific cell in DAX

 

 

5 REPLIES 5
FreemanZ
Super User
Super User

Easily in Excel, but seems not possible in DAX, where the data is evaluated column by column. If you look at the DAX codes, mostly refer to columns or tables. 

Anonymous
Not applicable

X & Y are given net conversion rates whist P & Q are Distrupts

 

For example

 

-0.020.75%
00.50%

 

Anonymous
Not applicable

I think its worth noting that the table would look like this, so would need more than a 2x2 table and i would like it to update when the data refreshes (if possible)

 

DisruptHeaderHeaderHeaderHeaderHeaderHeaderHeaderHeader
-0.02CellCell0.75%CellCellCellCellCell
CellCellCellCellCellCellCellCellCell
0CellCell0.50%CellCellCellCellCell

 

I think I need somehting to refer to a specfic cell in a way

ValtteriN
Super User
Super User

Hi,

There is a hidden function called OFFSET which can help you.

E.g.

Data:

ValtteriN_0-1670415243709.png

 


Dax:

Measure 25 =
var offsetindex= offset(1,ALLSELECTED('Table (15)'[Index]),orderby('Table (15)'[Index],asc))

var x = SELECTEDVALUE('Table (15)'[Column1])

var y =

CALCULATE(MAX('Table (15)'[Column1]),ALL('Table (15)'),'Table (15)'[Index]=offsetindex)

var p = SELECTEDVALUE('Table (15)'[Column2])

var q =
CALCULATE(MAX('Table (15)'[Column2]),ALL('Table (15)'),'Table (15)'[Index]=offsetindex) //calculate column2 value based on idex offset

return
if(ISBLANK(offsetindex),"NA",
DIVIDE(x,y)^(1/((p-q)*100))-1)


End result:
ValtteriN_1-1670416079628.png

 

There are also other ways to do this kind of calculation using idex, but here I am specifically using relation to other "cells" as the basis for calculation.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




NanDeb
Helper I
Helper I

I believe it could be possible... however, how are X&Y and P&Q related in the table? Is it just consecutive records, or dates ? Can you share  sample data

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors