Best Practice for Storing Commonly Used Variables / Parameters

[tomperro]

Is there a way to store commonly used variables in a table or other way?
For eample, I am using a measure to determine which employees are considered new hires.
Currently, I am looking for anyone that has been employed for 1 year or less and marking them as a new hire. 

If I decide that I want to make anyone employed for 2 years or less a new hire, I can modidy my formula but I would like to store the 1 year or 2 year filter in a table so I can make the change in just one place.

[Anonymous · Accepted solution]

Hi @tomperro ,

I create a new table.

Table = DATATABLE("NewHireThreshold",INTEGER,{{12}})

Then I create another table as you mentioned.

There is a  measure and here is the DAX code.

IsNewHire =
IF (
    DATEDIFF ( TODAY (), MAX ( 'Employee'[HireDate] ), MONTH )
        >= MAX ( 'Table'[NewHireThreshold] ),
    "Yes",
    "No"
)

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Khushidesai0109]

1. Create a Parameter Table: Create a new table in your Power BI model where you will store your parameters. This table will have at least two columns: one for the parameter name and one for its value.
   
   

2. Define Parameters: Populate this table with the parameters you want to use in your calculations. For example, in your case, you might have a row in this table with the parameter name "New Hire Threshold" and the value "1 Year".
   
   

3. Reference Parameters in Measures: Modify your measure to reference the parameter value from the parameter table. For example, instead of hardcoding "1 Year" in your calculation, you would reference the value stored in the parameter table.

Here's an example of how your parameter table might look:

Parameter Name Value

New Hire Threshold

1 Year

And here's how you might modify your measure to use this parameter:

New Hire Status =
VAR NewHireThreshold = SELECTEDVALUE(ParameterTable[Value])
RETURN
IF(
[Employee Tenure] <= NewHireThreshold,
"New Hire",
"Existing Employee"
)

This way, if you decide to change the threshold for new hires from 1 year to 2 years, you would just need to update the value in the parameter table, and all measures referencing that parameter will automatically reflect the change.



Please give thumbs up and accept it as solution if it helped you!!