In this tutorial, you will learn how to use the DAX function HASONEVALUE to conditionally control cell locking based on filter context.

Step 1: Understanding `HASONEVALUE`

The HASONEVALUE function returns TRUE when the context for columnName has been filtered to one distinct value. Otherwise, it returns FALSE.

Step 2: Creating the `Isfiltered` Measure

We will create a measure that checks if certain columns have a single value filter applied. If not, it will indicate that the context is not sufficiently filtered.

DAX Formula:

DAX

Isfiltered = IF( HASONEVALUE(dbo_V_DimOrganization[OrganizationName]) = FALSE || HASONEVALUE(dbo_DimScenario[ScenarioName]) = FALSE, 1, 0 )

Explanation:
- The IF statement checks two conditions using HASONEVALUE:
  - Whether dbo_V_DimOrganization[OrganizationName] has been filtered to one distinct value.
  - Whether dbo_DimScenario[ScenarioName] has been filtered to one distinct value.
- If either condition is FALSE, meaning more than one value or no value is selected, the measure returns 1.
- If both conditions are TRUE, the measure returns 0.

Step 3: Combining with an Existing Cell Lock Measure

Assuming you have an existing measure for cell lock status, you can combine it with the newly created Isfiltered measure to enforce additional filtering logic.

DAX Formula:

DAX

Cell_lock_filter = [LockCell] + [Isfiltered]

Explanation:
- This new measure sums the values of your existing cell lock measure [LockCell] and the Isfiltered measure.
- The combined measure will now consider both the original cell locking logic and the filter conditions.

Step 4: Implementing the Combined Measure

Add the Isfiltered measure to your model:
- Navigate to the data model and create a new measure.
- Insert the DAX formula for Isfiltered.
Combine with the existing cell lock measure:
- Create another measure for the combined logic using the Cell_lock_filter formula.

Example in Power BI

DAX

-- Measure for checking filter context

-- Combined measure for cell lock

Summary

By following this tutorial, you've learned how to:

Use the DAX function HASONEVALUE to check filter context.
Create a measure to indicate whether columns are sufficiently filtered.
Combine this measure with an existing cell lock measure to enhance your data model logic.

This approach ensures that your data model responds correctly based on the specificity of the filters applied, improving data integrity and accuracy.

Data Configuration Options in the accoPLANNING Visual

Introduction