Create a function with arguments to be called in a measure

[QR-]

Hello,

I am quite new to Power BI and I am working on a project to model the growth and change of various aspects of a portfolio. I am finding that in many of my measures I have very long expressions which I re-use in several places and I was hoping that there is a way to condense these down to just single functions or similar that I can call with an argument. 

One of these expressions looks like this:

SWITCH(TRUE(),[Growth Group] = 1 && [Roll Off 1 Value] <> 1,([RWA]*Power(1+ ([Growth Group 1 Value]/100),((MAX('Projections Growth Scenario'[p])-MAX('Projections Growth Scenario'[Run Quarters Since 1900]))/4))),[Growth Group] = 1 && [Roll Off 1 Value] = 1,0,[Growth Group] = 6 && [Roll Off 6 Value] <> 1,([RWA]*Power(1+ ([Growth Group 6 Value]/100),((MAX('Projections Growth Scenario'[p])-MAX('Projections Growth Scenario'[Run Quarters Since 1900]))/4))),[Growth Group] = 6 && [Roll Off 6 Value] = 1,0,[Growth Group] = 7 && [Roll Off 7 Value] <> 1,([RWA]*Power(1+ ([Growth Group 7 Value]/100),((MAX('Projections Growth Scenario'[p])-MAX('Projections Growth Scenario'[Run Quarters Since 1900]))/4))),[Growth Group] = 7 && [Roll Off 7 Value] = 1,0,[RWA])

For clarity:
RWA
Run Quarters Since 1900
Growth Group
p

The above are all columns in a data table. Each RWA entry will have a value for run quarters, growth group and p (the projected quarter). 

The "Roll Off [x] Value" and "Growth Group [x] Value" are all slicers, the roll off values can be either 0 or 1, and the growth group value can be from -100 to 100. They are not currently joined to the main data table by any joins. 

This expression is intended to allow different parts of a portfolio to grow at different rates or roll off. Currently I am modelling these with three of the most important growth groups, but eventually the logic will include at least 20-30 of them. If I keep going with this methodolgy this segment will become very long, and I will need to reference it in several places in my measures. 

It would be much better to store this expression in a single place, whether it be a measure or a function of some sort and be able to call it with arguments. This particular structure will not only be called against RWA, but against other columns in the table such as Balances, Profit etc... So it would be great if I can put this in the form of:

Growth_Sim(RWA) or something similar. 
Is this possible to do? 

[QR-]

Sure, thanks for your help bolfri! Have a great day!

[bolfri · Accepted solution]

Nice monster code. ^.^

I don't know the logic here, but if the rules are same and you only want to give different values depending on which scenario is used you can create one measure that return a value and refference to it.

Eg. if the conditions are same create a measure to check the scenario that you are dealing with:

Scenario checking = 
    SWITCH(TRUE(),
        1 > 2, "one",
        2 < 1, "two",
        3  = 3, "three",
        "other"
    )

And then create another measure that based on this scenario to give you the result that you need.

Another measure = 
SWITCH([Scenario checking],
"one","Gimme something",
"two","Gimme something else",
"etc.")

I don't know if this was what you were looking for.

[QR-]

Hi Bolfri, 

Thanks for this! It could potentially work as a solution, my only issue with it is that I would need to define them as a set of measures per table. Later in this project I will have separate tables which will be linked to different data sources with slightly different structures. So I would need to re-create these measures for those tables too, and then maintain them whenever the definitions might change.

I was hoping there was some way to create and store this logic somewhere where it could be referenced globally, in the same way as functions like "Calculate" and "Sumx" are referenced? 

[bolfri]

I don't think so. Right now it's not possible butconsider vote to that idea.

Refference:

https://community.powerbi.com/t5/Desktop/User-defined-functions-in-DAX/td-p/429338

https://ideas.powerbi.com/ideas/idea/?ideaid=10c215a5-f8c0-40d0-bdd8-0747c2e00be5

Have a nice day!