Forum Discussion

h4tt3n's avatar
h4tt3n
Helper V
6 years ago
Solved

Multiple global constants best practices?

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
Need Help
Show and Tell
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    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

8 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    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's avatar
      h4tt3n
      Helper V
      6 years ago

      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's avatar
        Jimmy801
        Community Champion
        6 years ago

        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