Tables In Direct Query / Hybrid Get Error when Querying Two Columns with Same Name different tables

[JaromG]

I can't query two columns with the same underlying name with direct query because of how power bi writes the query. Is there a solution to this?

The source column name for each of these tables is CalendarYearNum. 

In the model schema I renamed Calendar Year Num for Table 1 to EffectiveDate[EffectiveYear]

In the model schema I renamed Calendar Year Num for Table 2 to ExpirationDate[ExpirationYear]

Both EffectiveDate and ExpirationDate tables have a relationship 1-Many to a fact table on a datekey.

If I run a dax query like:

SUMMARIZECOLUMNS(
                            'EffectiveDate'[EffectiveYear],
                            'ExpirationDate'[ExpirationYear],
                            "Sum of Measure", [Some odd measure from fact table]
                        )

It creates a Snowflake query that looks like this which does not alias to unique names: 

SELECT TOP (1000001) [t13].[CalendarYearNum],
[t14].[CalendarYearNum],
COUNT_BIG(*) AS [a0]
FROM (
(
[FactTable__SYSTEMPARTITIONS__FactTable DQ] AS [t19]
INNER JOIN [ExpirationDate] AS [t13] ON ([t19].[ExpirationDateKey] = [t13].[DateKey])
)
INNER JOIN [ExpirationDate] AS [t14] ON ([t19].[ExpirationDateKey] = [t14].[DateKey])
)
GROUP BY [t13].[PRODUCER_NAME],
[t14].[PRODUCER_NAME]

The error I get is: (*Note that the column name is slightly different as I am not writing it verbatum above)

[Anonymous]

Hi @JaromG ,

Please consider using the ALLEXCEPT function in your DAX query to maintain the context of all columns except the ones causing the conflict. This approach might help in scenarios where you need to keep all other filters applied except for the conflicting column.
When using SUMMARIZECOLUMNS, you can try explicitly renaming the columns within the DAX query itself to ensure they have unique names in the resulting SQL query.
Since both EffectiveDate and ExpirationDate tables have a relationship to a fact table, ensure that these relationships are correctly set up to prevent any ambiguity in the query.
If the problem persists, could you please provide us with your sample data?

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