DAX Foreach loop, is it even possible?

[rbreneman]

Hi!

I'm not sure if this is possible to do with DAX or not, but figured I'd put it out here and see if anyone has any suggestions.

I have three tables. 1st table is called Engineers, 2nd table is called EngineerAssignments, 3rd table is called Opportunity. EngineerAssignments is filled using DAX, where it's searching a specific field in Opportunity (PS_Notes__c) and looking for an engineers name. If found, it's creating a row with the OpportunityID from the row in which it searched and the Engineer's name. This works perfectly, but the DAX is a manual process where I must enter the engineers name statically. I'd love to be able to pull this from the existing Engineers table where I already have the ShortName field. Something like a foreach where it can grab each row from the engineers table and search the opportunity table for that specific engineer. Make sense? It is possible for more than one engineer name to be found on a single opportunity so the end result table can end up with duplicate OpportunityIDs but with different engineers. Again, not sure if this is possible, but figured I would ask. Thanks in advance for any help or suggestions on how I can streamline this a bit!

EngineerAssignments Table

Engineers Table

[rbreneman]

Thanks everyone for the great suggestions and the well thought out explanations. I've learned a lot just from this post! Ultimately I went with Imke's solution as it seemed the most straight forward to plug into my existing dataset. Again, really want to express thanks on taking the time to build out a sample pbix as well as explain the solution. This community is awesome!

[v-lili6-msft]

hi  @rbreneman 

for your requirment, I would suggest you use this simple formula as below:

New table =
SELECTCOLUMNS (
    FILTER (
        Opportunity,
        Opportunity[PS_Notes__c] IN VALUES ( 'Engineers'[ShortName] )
    ),
    "OpptyID", Opportunity[ID],
    "Engineer", [PS_Notes__c]
)

Regards,

Lin

[AllisonKennedy]

@rbreneman
First, does this have to be done in DAX? Since you are still building the data model, I suggest using Power Query to tackle this. 

I have made a sample file based on your post. It is attached below the signature. I have written a custom function that essentially does the For Each loop you require. @ImkeF may know a more efficient way to do this? 

Click Transform Data in the Home tab to open Power Query and see if the input and results look similar to what you need. I have keep all the PS_Notes_c column from the Opportunity table in the Engineer Assignments just to make it easier to verify that it worked, but you can easily change that to just pull the Opportunity ID.

Here is the custom function (you will need to update names and references to make it work with your data): 

= (ShortNameSample as text) => let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY67DoMwDEV/xcrMTxBg6NAOZeiAGNLUtBYmroIl4O/r0sfme3xk365zpSvceQsJPLDISOkOYVDMsJA+4DCHEOFkTrngLBO6vuict/zdgApEmZ6MikZ9JmYKSXevMtJqyIo3uG5wpBGhNVaJ8C7UFv60xoES6edF86t1saFZIzLj+2r/Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Opportunity ID" = _t, PS_Notes__c = _t, #"Opportunity Name" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Opportunity ID", type text}, {"PS_Notes__c", type text}, {"Opportunity Name", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Is Assigned", each Text.Contains([PS_Notes__c], ShortNameSample)),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Is Assigned] = true)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Is Assigned"})
in
#"Removed Columns"

If your PS_Notes__c column were cleaner, you could also do this using a Left Outer Join and Merge. 

Otherwise, @Greg_Deckler  may have a DAX Solution for this and has written some Quick Measures on Loops in DAX that he explains in this article: https://community.powerbi.com/t5/Community-Blog/For-and-While-Loops-in-DAX/bc-p/637511

Here are the Quick Measures:  

https://community.powerbi.com/t5/Quick-Measures-Gallery/For-Loop/m-p/637531#M319

https://community.powerbi.com/t5/Quick-Measures-Gallery/While-Loop/m-p/637535#M320

[ImkeF · Accepted solution]

Hi @AllisonKennedy ,

not sure if the performance of my approach would actually be faster, but code-wise it's a bit lighter. I would "simply" add a column to the opportunities table like so:

Table.AddColumn(Opportunity, "IncludedEngineers", each List.Select(Engineers[Short Name], (l) => Text.Contains([PS_Notes__c], l)))

Would also buffer the Engineers table. See attachment.

[AllisonKennedy]

Thanks @ImkeF
That's awesome! I need to practice more using functions that take a 'function' as argument. So basically you're defining the (1) as the parameter for the function, but that could be replaced with any value (name) or anything right?

The Microsoft Docs documentation uses this "each _ " underscore syntax:
List.Select({1, -3, 4, 9, -2}, each _ > 0)
Does that only work for simple operators or would you be able to use that with a nested function like Text.Contains?

I might pick your brain some day on Table.Buffer as well. I think I get it, but I still do a bit of trial and error to see when it helps and when it doesn't. Am I right in thinking that in this example, if you don't have the Table.Buffer on the Engineers table, it will return to data source to load the Engineers table repeatedly for every row in the Opportunity/Imke table, but with Table.Buffer it will just load Engineers table into memory completely, and then use that memory for each row of Opportunity/Imke. Is that right? Since we're only using the Engineers[Short Name] column would Table.Buffer ever decrease performance, for example if we had many many columns in the Engineers table?

Sorry for all the questions and hope they don't confuse anyone else.

[ImkeF]

Hi @AllisonKennedy ,

yes, you could use any string as a parameter in the function. The "each"-keyword is just syntax sugar for a proper full function declaration. It allows you to use the underscore or even no sign at all to reference the value that has been automatically passed to it. Like if you just use square brackets to reference a columnm.

2nd yes as well: Buffer should prevent re-loading. But of course the buffering has other effects as well. So if you're buffering a large table (like I would imagine the opportunities table would be), then this could be a hit on performance as well.
And at the end you must have in mind that Power Query creates its own execution plan for the query (just like SQL or VertiPaq). So you almost never know what's better after you have tried.