Join 2 tables filtering data on one of the tables using DAX query

[bhavya11]

Hello,

I have a paginated report based on a PBI Semantic Model in DirectQuery mode. There are 3 tables in the semantic model with a relationship between them.

Table1

EmpId, other columns......

Table2 

EmpId, other columns......

Table3

EmpId, other columns.....

Relationship is established in the semantic model between Table2 & Table1 and Table3 & Table1 based on EmpId column. Both the relationships are many-to-many.

Now I need to do something like below:

Select * from Table1 t1

JOIN Table2 t2 on t1.EmpId = t2.EmpId

JOIN Table3 t3 on t1.EmpId = t3.EmpId

WHERE t2.one_of_the_columns = somevalue and t3.one_of_the_columns = somevalue

As my the PBI Semantic Model is in DirectQuery mode, I am mandated to write the query of my paginated report in DAX. It does not support T-Sql.

I have written the following DAX query

EVALUATE
FILTER(Table1, Table2[one_column]=somevalue && Table3[one_column]=somevalue)

As the PBI Semantic model already has relationship established, the above expression should work. DAX should be able to establish JOIN based on the relationship.

But it gives the following error: 

A single value for column '<oii>xxxxx</oii>' in table '<oii>xxxxxx</oii>' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

I also tried explicitly making a join by:

EVALUATE
FILTER(Table1, Table1[EmpId]=Table2[EmpId] && Table1[EmpId]=Table3[EmpId] && Table2[one_column]=somevalue && Table3[one_column]=somevalue)

but it gives the same error.

I have also tried other options for JOIN such as SELECTCOLUMNS, ADDCOLUMNS but does not work.

I have also tried using RELATED.

EVALUATE
FILTER(Table1, RELATED(Table2[one_column])=somevalue && RELATED(Table3[one_column])=somevalue)

 It gives the following error:

The column '<oii>xxxxxx</oii>[<oii>xxxxx</oii>]' either doesn't exist or doesn't have a relationship to any table available in the current context.

though a relationship exists. It gives the same error when I use NATURALINNERJOIN.

Any guidance is highly appreciated.

Thank you

[johnt75]

Try

EVALUATE
CALCULATETABLE (
    Table1,
    Table2[one_column] = somevalue,
    Table3[one_column] = somevalue
)

[DataNinja777 · Accepted solution]

Hi @bhavya11 ,

In your paginated report, you're trying to filter Table1 based on conditions from Table2 and Table3, with all tables linked through many-to-many relationships on the EmpId column. Since the Power BI Semantic Model is in DirectQuery mode, the query must be written in DAX instead of SQL. However, your current attempts using FILTER, RELATED, and NATURALINNERJOIN have resulted in errors, primarily because DAX requires careful handling of relationships, particularly in the context of many-to-many joins.

The error you're encountering, "A single value for column cannot be determined," occurs because DAX is attempting to evaluate a column from another table without a clear context or aggregation. To resolve this issue, the solution involves using CALCULATETABLE to apply cross-table filters while leveraging the existing relationships in the model. Here's how you can write the query:

EVALUATE
CALCULATETABLE(
    Table1,
    FILTER(Table2, Table2[one_column] = "somevalue"),
    FILTER(Table3, Table3[one_column] = "somevalue")
)

This query works by applying filters from Table2 and Table3 within the context of CALCULATETABLE, which then propagates these filters to Table1 based on the established relationships. Since the relationships are already defined in the semantic model, there is no need to explicitly join the tables in the query. The FILTER function applies the conditions from Table2 and Table3, and the relationships ensure that these filters affect Table1.

If the above approach does not work as expected due to model constraints or if you're looking for an alternative, you can use the TREATAS function to achieve a similar result by simulating cross-table filters. Here's an example:

EVALUATE
CALCULATETABLE(
    Table1,
    TREATAS(
        FILTER(Table2, Table2[one_column] = "somevalue"),
        Table1[EmpId]
    ),
    TREATAS(
        FILTER(Table3, Table3[one_column] = "somevalue"),
        Table1[EmpId]
    )
)

In this query, TREATAS creates a filter context on Table1 using the values from Table2 and Table3. This method can be useful if there are complications with filter propagation through the many-to-many relationships.

If you prefer a more SQL-like join structure, you can try using NATURALINNERJOIN. This function combines two tables by matching their common columns. However, it is generally more limited and may require additional filtering to achieve the desired result:

EVALUATE
FILTER(
    NATURALINNERJOIN(
        NATURALINNERJOIN(Table1, Table2),
        Table3
    ),
    Table2[one_column] = "somevalue" &&
    Table3[one_column] = "somevalue"
)

This query performs an inner join between Table1, Table2, and Table3, and then applies the necessary filters. While NATURALINNERJOIN can simplify the join syntax, it may not be as efficient or flexible as using CALCULATETABLE with filters.

In summary, CALCULATETABLE is often the most reliable approach in scenarios involving complex relationships and filtering across multiple tables. The relationships defined in your Power BI Semantic Model should ensure that the filters from Table2 and Table3 propagate correctly to Table1, provided the query is structured to respect DAX’s filter context requirements. If additional assistance is needed to address any specific error, let me know.

Best regards,

[bhavya11]

Thank you. The CALCULATETABLE worked like a charm.

Thank you for the detailed explanation on each query.