Forum Discussion
Help DAX: Nested IFs with looking up previous values (Excel formula included)
Hi Everyone,
I need help to add Helper column in my table.
F2 = IF(AND(A2=A1,B2=B1,F1="Yes"),"Yes",IF(OR(COUNTIF(C2,"*star*"),COUNTIF(C2,"*moon*")),"Yes","No"))
This is the formula I have for Helper column. How can I make it work with Dax? What would be the easiest way to do it? I need to apply that to 180k rows.
| Name | Ver | sName | sStep | Index | Helper |
| ABAB | D001 | CC-001 A | 1 | 1 | No |
| ABAB | D001 | CC-021 B | 2 | 2 | No |
| ABAB | D001 | CC-031 C | 3 | 3 | No |
| ABAB | D001 | CC-041 Star | 4 | 4 | Yes |
| ABAB | D001 | CC-051 D | 5 | 5 | Yes |
| ABAB | D001 | CC-61 E | 6 | 6 | Yes |
| ABAB | D001 | CC-62 F | 7 | 7 | Yes |
| ABAB | D002 | CC-001 A | 1 | 8 | No |
| ABAB | D002 | CC-021 B | 2 | 9 | No |
| ABAB | D002 | CC-031 C | 3 | 10 | No |
| ABAB | D002 | CC-041 Star | 4 | 11 | Yes |
| ABAB | D002 | CC-051 D | 5 | 12 | Yes |
| ABAB | D002 | CC-61 E | 6 | 13 | Yes |
| ABAB | D002 | CC-62 F | 7 | 14 | Yes |
| BDBD | D001 | CD-001 A | 1 | 15 | No |
| BDBD | D001 | CD-021 B | 2 | 16 | No |
| BDBD | D001 | CD-031 C | 3 | 17 | No |
| BDBD | D001 | CD-041 Moon | 4 | 18 | Yes |
| BDBD | D001 | CD-051 D | 5 | 19 | Yes |
| BDBD | D001 | CD-61 E | 6 | 20 | Yes |
| BDBD | D001 | CD-62 F | 7 | 21 | Yes |
| BDBD | D001 | CD-71 G | 8 | 22 | Yes |
| BDBD | D001 | CD-82 H | 9 | 23 | Yes |
Thanks in advance!
I was able to get it to work quickly by creating a summary table to use as a lookup to find the index value where the row contains "Star" or "Moon" then use that as a lookup against the main table.
Lookup Table code (go to modeling > new table and paste this in) you will have to change the name to match your data table.
IndexLookup = CALCULATETABLE( SUMMARIZE( YourTable , YourTable[Name] , YourTable[Ver] , YourTable[Index] ), CONTAINSSTRING ( 'YourTable'[sName] , "Moon" ) || CONTAINSSTRING ( 'YourTable'[sName] , "Star" ) )This generates a table that looks like this:
Then we use that in the main table and compare the index value where the line contains "Moon" or "Star" to the index of the row to populate the Helper Column.
Helper Column = VAR _Name = YourTable[Name] VAR _Ver = YourTable[Ver] RETURN IF ( YourTable[Index] >= CALCULATE( FIRSTNONBLANK(IndexLookup[Index], TRUE()), FILTER ( IndexLookup,IndexLookup[Name] = _Name && IndexLookup[Ver] = _Ver) ), "Yes","No")The column "Helper" in the image is uploaded from my excel file with 180k rows using your calc so I could compare it to the [Helper Column] calculated column to see that they match. It runs really quickly.
My sample file is attached for you to look at.
9 Replies
Hello EZiamslow
I believe this will work for you but you will have to change the table name to match yours
Basically it counts all the rows where
- the Name and Ver are the same as the current row,
- the sName contains "Star" or "Moon"
- the index is <= the current row
If that count is > 0 then Yes otherwise No. The image contains your example helper column [Helper Example] and the result of the formula [Helper Column]:
Helper Column = VAR _Name = 'DataTable'[Name] VAR _Ver = 'DataTable'[Ver] VAR _Index = 'DataTable'[Index] RETURN IF ( CALCULATE ( COUNTROWS ( 'DataTable' ), ALL ( 'DataTable' ), 'DataTable'[Name] = _Name, 'DataTable'[Ver] = _ver, CONTAINSSTRING ( 'DataTable'[sName], "star" ) || CONTAINSSTRING ( 'DataTable'[sName], "moon" ), 'DataTable'[Index] <= _Index ) > 0, "Yes", "No" )jdbuchanan71 Your calculated column works when I work with smaller sample size. However, when I run it on my data table, I get not enough memory error because I have 180k rows. I have 16gb. Anything I can do to make it work?
I tried it with a tighter ALL against 180k rows and still ran out of memory.
Helper Column = VAR _Index = 'DataTable'[Index] RETURN IF ( CALCULATE ( COUNTROWS ( 'DataTable' ), ALLEXCEPT( 'DataTable', 'DataTable'[Name], 'DataTable'[Ver] ), CONTAINSSTRING ( 'DataTable'[sName], "star" ) || CONTAINSSTRING ( 'DataTable'[sName], "moon" ), 'DataTable'[Index] <= _Index ) > 0, "Yes", "No" )What is your data source? If it is coming from an excel file it might be best to leave the helper calc on the excel side.
