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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
EZiamslow
Helper II
Helper II

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

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

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

 

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

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

 

@jdbuchanan71 

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

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

 

 

 

 

 

 

 

 

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

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

 

@jdbuchanan71 

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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