FOR loop equivalent in DAX?

[Anonymous]

Hi all,

I'm looking to get feedback on how I could acheive something similiar to a loop in DAX. I'm familiar with SWITCH and already using it (as you can see in the code below) however I'm faced with a significant scaling issue.

Per the code below I have three variables I need to evaluate for each condition to use a specific measure when that condition is met. There are 8 Sites, 6 Care Settings and each Care Setting has about 6 Categories (varies). So the current code sample is a smaller representation of something I need to scale significantly. Does anyone have suggestions on how I can dyanimcally iterate through the various comibinations or make use of more variables? Can I "create" a measure name based on variables? Other suggestions on all the conditions? If I was in C# or VB (not that I'm terribly profecient in either) I would use a loop to iterate through all the possible combinations and set the values properly.

Thoughts? Ideas? Trying to avoid a huge chunk of time writing a ton of repetitive code and the associated risk of making mistake(s).

Thanks!

% of Prog = 
//variables for Columns
    VAR CareSetting = MAX(prog_SiteProgram[prog_CareSettings.ServiceName])
    VAR Category = MAX(prog_SiteProgram[prog_Categories.Name])
    VAR Site = MAX(prog_SiteProgram[site_Sites.Abbreviated Name])

RETURN
    SWITCH(
        TRUE(),

        CareSetting = "Acute Care" &&
        Category = "Ambulatory" &&
        Site = "GH",
        [1.acu.a Value],

        CareSetting = "Acute Care" &&
        Category = "Inpatient" &&
        Site = "GH",
        [1.acu.i.%Prog],

        CareSetting = "Acute Care" &&
        Category = "Diagnostic" &&
        Site = "GH",
        [1.acu.d Value],

        CareSetting = "Acute Care" &&
        Category = "Treatment" &&
        Site = "GH",
        [1.acu.t Value],

        CareSetting = "Administration" &&
        Category = "Administration" &&
        Site = "GH",
        [1.admin Value],

        BLANK()
    )

Note, in case you're wondering "what the heck is this guy doing", the reason for what appear to be individual measures being applied to each row is the values are coming from indiviual WhatIf Parameters driven by "sliders" (slicers). So I have to tabulate all those unique values into a table so I can do further calculates based on the values set by the end user. I've proven out the majority of my infrastructure, but now I've looped back to scaling this out for the whole data model.

[Greg_Deckler]

Refer to https://gregdeckler.com

[Anonymous]

@Greg_Deckler I'll take a look.

Does this mean you don't think I'm crazy, and there is a ray of hope?

Thanks,

-R

[Greg_Deckler]

Well, I wrote a blog article on how to emulate a FOR loop and a WHILE loop in DAX. Also an article on emulating recursion. You're not crazy.

[Anonymous]

Hi @Greg_Deckler ,

Read through the blog post, generally makes sense without going and writing my own.

In thinking my situtation through though, I think I'm still stuck...

Using your approach here is how I think it would play out for me.

I have a table with an index that by virtue of its columns defines all the various combinations (rows). Using your approach we would iterate through each row, and for each row I could use the MAXX formulas to find the values for those rows (my conditions). Great, now I could say "do X" for this particular condition, and I didn't have to spell out each condition. Where I think I'm going to get stuck though is the "do X" I want is to assign a specific measure to that condition and I don't see a way to (through logic) specifiy use this Measure for this condition unlees there is a clever way to just add a bunch of measures to a single column in table and index it, then I would be able to say for Index 1 of my first table, match that to Index 1 of the Measure Table. The whole intent of this execise is to align each unique measure to the particular row so that I can then proceed to carry out more "normal" tabular calculations like sums, divides, multiplies, etc.

Am I missing something?

[Greg_Deckler]

@Anonymous - It's very difficult to say how this would work in your situation without seeing 1. Sample data and 2. Expected output from sample data.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

https://stantec-my.sharepoint.com/:f:/p/robert_manna/EnbE-K84tNlHofWg91AVbvsBTw4VIFOkFbkvxbbaZK9Osg?...

Hello all,

A sanitized version of my PBIX and data model are here (the link expires on 4/12, I'll try to renew it, if not post in this thread: https://stantec-my.sharepoint.com/:f:/p/robert_manna/EnbE-K84tNlHofWg91AVbvsBTw4VIFOkFbkvxbbaZK9Osg?... ), screen shot with mark-ups below.

• The data model is built as a fairly simple relational model, Excel was used for convience and portability
• The goal of this exericise is to allow an end user to distribute "Program" (area for specific uses) to different Sites using WhatIf parameters
  • There is a certain amount of Program 'prog_ProposeProg'[Target Area]
  • The Sites each have a certain amount of capcity 'site_Capacities'[Max GFA]
  • The program is broken down into settings and then categories (the data actually breaks down to subcategories, but we're generally not concerned with that level of granularity). Some categories are dependent on others 'prog_Categories'[Dependent]
• So for each Site, Setting and (non-dependent) Category there will be sliders to allow user input/adjustment.
  • Those sliders must then each be correlated with the proper row (Site, Setting, Category) so the remaining calculations and visualizations can be carried out, for example summing all area assigned to specific site, or illustrating the percentage distribution of program accross all the sites, etc. A key goal of this model is to ultimatly show "busts" if too much program is assigned to a specific site, therefore exceeding the SUMX('site_Capacities', [Max GFA]).
  • Dependent Categories are assigned an "Average" based on related Dependent Categories, for example [1.acu.avg] found under 'Dependent % Area Measures'.
• There is a table set-up to correlate Site, Setting, Category, and Area: 'prog_SiteProgram' which is where the measures need to be applied in-line for each row of the table.
  • Currently I have a Measure with the SWITCH function set-up with variables (see previous post or PBIX): [% of Prog]
  • This measure is already 100+ lines, with only a small sub-sample of the WhatIf parameters built-out. With out figuring out a way to more econimcally iterate through and assign the WhatIf parameters (Measures) to the appropriate row, I estimate I'm looking at at least 500 lines in this single measure with plenty of opprotunity for error, even with the use of variables and copy/paste (oh how I wish Power BI had a true CDE).
• If you have suggestions on how I else I might approach this challenge, I'm open to suggestions. The goal is something highly portable and relatively modifieable that we can hand over to the client. The interactive feedback piece is crucial though I have been wondering if I should take a look at Power Apps in combination with Power BI, but that would make "portability" more complicated. Could or should this just be written as a WebApp with a simple DB back-end of some kind, maybe, probably, but again that impacts portability and easy of modification by the client/end user.
• Other suggestions or observations are welcome (I'm not Power BI / DAX expert).

[Anonymous]

Giving this a tickle now that the Holiday weekend is wrapped up for most (see post above). I was hoping @Greg_Deckler might have some additional feedback or thoughts.

Thanks for looking!

[Anonymous]

Yeah.... I know. Creating a sample model will take some effort, but I will undertake that as soon as I get through my deadline today. I do appreciate your quick responses and assistance.