Multiple global constants best practices?

[h4tt3n]

Hello folks,

Experienced programmer but Powert Query / DAX beginner here. I need to somehow define and manage a number of global constants in my Power BI workspace and would like your suggestions on best practices. It seems like a no-brainer to just add a single-row table with one constant in each column, but Power Query and DAX are surprisingly poor at handling cell-level operations, so for retrieving the actual values I haven't come up with anything better than:

MAX(const_table[const_x])

which is way too clunky. The two other options I can see are to store them either as parameters or measures. What do you suggest? Simplicity of use and shortness of syntax has priority.

Cheers & Happy Holidays, Mike

[Jimmy801]

Hello @h4tt3n 

as I know you can't define constants and pass them from Power Query to DAX. You can define a constant in Power Query by using a blank query. In DAX you can use a measure or a table. However it's not possible to use a constant in an environment outside of a single file.

Hope this helps

Jimmy

[h4tt3n]

Hello @Jimmy801 

Thanks for the quick answer, although I'm a bit puzzled. I can't possibly be the first person to need this functionality? If we disregard PQ and concentrate on DAX, what would be the best practice for  managing multiple constants that are visible to other DAX commands / queries? My predcessor (after which I am cleaning up) simply definded an entire single column, single row table for each variable, which to me seems as pretty bad coding practice, and which heavily clutters up the model view of the workspace.

Cheers, Mike

[Jimmy801]

Hello @h4tt3n 

you can use variables within a measure like this:

testvar = VAR test = SUM('Table (2)'[Column1]) return test

you can then reuse that measure within other objects like this

othermeasure = [testvar]

What would you like to achieve exactly?

Jimmy

[h4tt3n]

Hello @Jimmy801 

Measure / calculated column scoped variables are neat too, but not quite what I am looking for. What I want to achieve is simply a group of constants, as in a literal number or word, which is accessible to the measures and calculated columns in the Power BI workspace. One example of a task is to estimate bacterial growth in hot-water pipes based on flow and temperature, and all the equations used for this need to access the same global constant min/max temperature and flow rates in order to make the estimation. The alternative would be to hard-code the values into each equation, which is a nightmare and very bad coding practice.

Cheers, Mike

[Jimmy801 · Accepted solution]

Hello @h4tt3n 

sorry, here I can't e of any help. But maybe there are some possibilities out there. Maybe someone else can help. You can also think of posting in the DAX-section of this forum.

What you can do for sure due is to create a datatable with power query and then hand over this data into DAX and access there. What do you think of that? Here an example

1. Create query called "Constants" with datatable like this

let
    Constants= #table(type table [ConstantName=  text, ConstantValue=  any ], {{"ConstantA",1}, {"ConstantB", 2}})
in
    Constants

 2. Create a Measure that reads one constant like this

ConstantA = LOOKUPVALUE(Constants[ConstantValue];Constants[ConstantName];"ConstantA")

 3. Use this measure in your environment (here in a new column)

ConstantA = LOOKUPVALUE(Constants[ConstantValue];Constants[ConstantName];"ConstantA")

 4. External reference would then look like this

Column = Constants[ConstantA]

What do you think of this solution?

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[h4tt3n]

Hello @Jimmy801 

After having just discovered that you can create and manage parameters in the query editor, which is exactly what I was looking for - only to discover that you can't in any way reference them directly in DAX (?!?) - I am inclined to think that your solution might in the end be the best practice. 

Is there any way you can think of to access or reference the value of a query editor created parameter (not a what-if parameter) inside a DAX calculated column or measure?

Cheers, Mike

[Jimmy801]

Hello @h4tt3n 

exactly, parameter can only be used in power query internally.

However you can create a Query called "ConstantA" and input in the advanced the parameter only. But this woulb be of no benefit, as you can input the desired parameter value also in the advanced editor directly. But with this you would have to create for every constant a new query. I would go for the datatable version

Jimmy

[h4tt3n]

@Jimmy801 

I agree. I will use a table containing the constant values and then reference them through measures of the same name. It's backwards and will clutter up the Fields view, but it seems to be the least bad solution 🙂 Thank you very much for taking the time answering this question. 

Cheers, Mike