cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EZiamslow Regular Visitor
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.

 

NameVersNamesStepIndexHelper
ABABD001CC-001 A11No
ABABD001CC-021 B22No
ABABD001CC-031 C33No
ABABD001CC-041 Star44Yes
ABABD001CC-051 D55Yes
ABABD001CC-61 E66Yes
ABABD001CC-62 F77Yes
ABABD002CC-001 A18No
ABABD002CC-021 B29No
ABABD002CC-031 C310No
ABABD002CC-041 Star411Yes
ABABD002CC-051 D512Yes
ABABD002CC-61 E613Yes
ABABD002CC-62 F714Yes
BDBDD001CD-001 A115No
BDBDD001CD-021 B216No
BDBDD001CD-031 C317No
BDBDD001CD-041 Moon418Yes
BDBDD001CD-051 D519Yes
BDBDD001CD-61 E620Yes
BDBDD001CD-62 F721Yes
BDBDD001CD-71 G822Yes
BDBDD001CD-82 H923Yes

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

@EZiamslow 

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:

IndexLookup.jpg

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.

HelperColumn2.jpg

My sample file is attached for you to look at.

 

 

 

 

 

 

 

 

View solution in original post

9 REPLIES 9
Super User
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"
    )

HelperColumn.jpg

 

EZiamslow Regular Visitor
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
Super User

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

@EZiamslow 

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.

 

EZiamslow Regular Visitor
Regular Visitor

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

@jdbuchanan71 

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

Super User
Super User

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

@EZiamslow 

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:

IndexLookup.jpg

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.

HelperColumn2.jpg

My sample file is attached for you to look at.

 

 

 

 

 

 

 

 

View solution in original post

Highlighted
EZiamslow Regular Visitor
Regular Visitor

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

@jdbuchanan71 

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

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

@EZiamslow 

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/

 

EZiamslow Regular Visitor
Regular Visitor

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

@jdbuchanan71 

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

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

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)