Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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.
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
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.
Thanks for trying. Data coming from SQL server. I might need to invole DBA to help with this on SQL side.
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.
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!
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/
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")
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |