Forum Discussion
Trying to create a DAX syntax
I am new in power bi so not exactly sure how to perform this syntax in DAX.
I am able to complete the formula in excel which looks like this.
IF(A2=A1,0,A2) So basically I want to know how to get the cell to return the value unless the value above it is the same.
Can someone please help me with how to perform this in DAX syntax? Thank you!
| Words | Calculation |
| 9564 | 9564 |
| 546 | 546 |
| 15648 | 15648 |
| 15648 | 0 |
| 15648 | 0 |
| 942 | 942 |
| 1354 | 1354 |
| 1354 | 0 |
| 1354 | 0 |
| 948 | 948 |
| 948 | 0 |
| 1000 | 1000 |
| 1000 | 0 |
You can't really work with "cells" in Power BI, you have to work with tables and columns. So, you will need to add an index to your table. Then you can filter down to the correct row above your current row, so something along the lines of:
Column = VAR __lastIndex = [Index]-1 VAR __currentValue = [Words] VAR __lastValue = MAXX(FILTER(ALL(Table),[Index]=__lastIndex),[Words]) RETURN IF(_currentValue = __lastValue, 0, __currentValue)
4 Replies
You can't really work with "cells" in Power BI, you have to work with tables and columns. So, you will need to add an index to your table. Then you can filter down to the correct row above your current row, so something along the lines of:
Column = VAR __lastIndex = [Index]-1 VAR __currentValue = [Words] VAR __lastValue = MAXX(FILTER(ALL(Table),[Index]=__lastIndex),[Words]) RETURN IF(_currentValue = __lastValue, 0, __currentValue)
Thank you for your response. I was wondering if you could walk me through how to create an index in my table. Here is a snapshot of my data I am working with.
In Power Query, select your query. Choose Add Column from the menu and then Index Column.