Certain techniques can be useful in writing DAX expressions that can utilize disconnected tables and slicers. Disconnected tables can act as custom slicers or filters that do not require the passenger loading of the specified filtering tables in the data model, which is perfect in instances where the existing relations are not required or are too complicated.
Employ the VALUES and SELECTEDVALUE Functions:
Start by getting the values from your disconnected slicer table. For example, if you have a table named “Filter Table,” add a slicer column, and then you will be able to call SELECTEDVALUE(‘Filter Table’[ColumnName]) from your DAX.
Use the filter and calculate functions for context manipulation:
In such a case of no direct relationships between tables connected, it becomes necessary to create context manually by using the CALCULATE function together with FILTER. For instance, in cases where the data table is to be filtered with respect to a resized value in the slicer, you would do something like this:
CALCULATE(
[Your Measure],
FILTER('Data Table', 'Data Table'[Column] = SELECTEDVALUE('Filter Table'[ColumnName]))
)
This enables you to use the selection made from the slicer as a filter when presenting the data table.
The TREATS function can be used effectively for similar behaviors like relationships:
TREATAS comes in especially handy in scenarios when the user would like to create relationships between two tables that are not physically related in the model. This lets you take data from any table and its columns and use that data as if that table were related to this one. For example:
CALCULATE(
[Your Measure],
TREATAS(VALUES('Filter Table'[ColumnName]), 'Data Table'[RelatedColumn])
)
In this case, TREATAS simulates a relationship by taking the values from the slicer table and treating them as if they belong to the data table, enabling slicer filtering.
DAX Studio Debugging:
Use DAX Studio to confirm the filter context and understand how your DAX calculations work with disconnected tables. This application is used for troubleshooting and simplifying expressions that are difficult to apply in complex models.
Disabling Relationships: In Power BI, TREATAS, VALUES, FILTER, and CALCULATE work in line to enhance the use of disconnected tables, making it possible to filter and analyze variations without relationship direct.
