Global constants best practice?

[h4tt3n]

Hello folks,

Experienced programmer but 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. This is what has been tried so far:

(1) My predecessor (after which I am cleaning up) simply created an entire table containing just one cell with the constant value. I really don't want to use this solution, as it is bad coding practice and clutters the model view in Power BI.

(2) My first solution was to create a single-row table for global constants, with one cell for each column containing one value corresponding to column name. THe problem here is that referencing the value has a clunky syntax, like this:

MAX(const_table[const_x])

(3) My next attempt was to store each constant in an appropriately named measure, which technically works, and has simpler syntax, but is harder to manage, since you can't get the same overview of multiple variables at once, like with a table.

(4) Last attempt was to create a group of parameters (not what-if's)  in the Query editor, which is clean, simple, and easy to manage. But then I discovered that there for unfathomable reasons aren't any way to reference these parameter values in DAX calculated columns or measures (?!?)

So, now I'm at my wit's end. Please help me with a good suggestion for managing a group of global constants.

Cheers & happy holidays, Mike

[kentyler]

A fun question.

As a side thought, you can hide tables and fields by using the "hide in report view" option that you get when you right click on them in the model view.

Power BI shows a lot more redundancy that SQL Server and sometimes its hard to deal with your built in instincts. You could create a "technical" table and then add your measures as calculated columns. that would give you a way to view all your measures at once. You can use the "enter data" button on the home ribbon and just enter one dummy value to create the table, or you can use the "new table" button on the modeling ribbon and use the table literal syntax MyTable= {"x"}

I'm a personal Power Bi Trainer I learn something every time I answer a question

The Golden Rules for Power BI

1. Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
2. Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
3. Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
4. Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result  to check on your steps along the way.

[kentyler]

I was curious about your saying that you could not find a way to reference parameters created in the query editor.

I found a post that pointed out that you need to check "enable load" for them 

Then you can reference them like this calculated column example: 

Column = MAX(Parameter1[Parameter1])
The MAX is clunky, but Power  BI uses a column based engine, rather than a row based one, so some function to tell it to select just one value instead of the whole column is always necessary.