cancel
Showing results for
Did you mean:
Regular Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Help DAX: Nested IFs with looking up previous values (Excel formula included)

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

## Re: Help DAX: Nested IFs with looking up previous values (Excel formula included)

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

Regular Visitor

## Re: Help DAX: Nested IFs with looking up previous values (Excel formula included)

@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?

Super User

## Re: Help DAX: Nested IFs with looking up previous values (Excel formula included)

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.

Regular Visitor

## Re: Help DAX: Nested IFs with looking up previous values (Excel formula included)

Thanks for trying. Data coming from SQL server. I might need to invole DBA to help with this on SQL side.

Super User

## Re: Help DAX: Nested IFs with looking up previous values (Excel formula included)

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.

Highlighted
Regular Visitor

## Re: Help DAX: Nested IFs with looking up previous values (Excel formula included)

Thank you so much for your help. I'm sure it will work. I will be testing it later today as I have some other project to finish. After checking my data and I need to make small changes on one of the criteria. I need to show +1 of current "Moon" index. For example, if one of the "Moon" Index is 51 but I want 52 instead of 51 showing on my summary table.

Where would be the best place for me to learn DAX? I'm planning to take mastering DAX from sqlbi. I really need to start taking classes. Thanks for your help!

Super User

## Re: Help DAX: Nested IFs with looking up previous values (Excel formula included)

SQLBI also offers an intro to DAX and that one is free.  It is a good place to start.  I have taken the Mastering DAX course and it is also very good.

https://www.sqlbi.com/p/introducing-dax-video-course/

Regular Visitor

## Re: Help DAX: Nested IFs with looking up previous values (Excel formula included)

Do you think you can help me out with one last request that I posted above?

Super User

## Re: Help DAX: Nested IFs with looking up previous values (Excel formula included)

Yep, we just need to change the helper column measure to be > the lookup index rather than >=

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

Announcements