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
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 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
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
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