Forum Discussion
Multiple global constants best practices?
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 Constants2. 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
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
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
Hello h4tt3n
you can use variables within a measure like this:
testvar = VAR test = SUM('Table (2)'[Column1]) return testyou can then reuse that measure within other objects like this
othermeasure = [testvar]What would you like to achieve exactly?
Jimmy
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
