Error, Too many arguments were passed to the NATURALLEFTOUTERJOIN function. The maximum argument count for the function is

Question

EVALUATE
FILTER
(
SUMMARIZE (
NATURALLEFTOUTERJOIN (
    'Target',
    'Target_Category',
    --'Target_Form'
    ),
'Target'[Area],
'Target'[id],
'Target'[Target date],
'Target'[Target Time Range],
'Target_Category'[Origin],
'Target_Category'[Sectotion],
'Target'[Location],
'Target_Category'[Ethencity],
'Target_FormResponse'[Area Used],
'Target'[Description]
),
'Target'[id] = Value("111373268") 
)
ORDEr BY 'Target'[Target Key]

I'm trying to query an SSAS tabular cube.

I have the above sample DAX code, in the NATURALLEFTOUTERJOIN function i'm joining tables 'Incident' and 'Incident_Category' such that i can be able to get columns from both tables.

However, i also need to get columns from a third table 'Target_Form' in the same DAX Query but when i try to add the third table in the NATURALLEFTOUTERJOIN function, i get the below error.

Too many arguments were passed to the NATURALLEFTOUTERJOIN function. The maximum argument count for the function is 2.

Does any any one know how i can join more than two tables that have a relationship such that i can work with columns in three or more tables in the same query?

Ok, now if i comment out 'Target' and un comment 'Target_Form', i'm able to select columns in 'Target', 'Target_Category' and 'Target_Form' tables. I'm new to DAX and SSAS and i'm wondering why am able to get columns in table 'Target' which i have commented out in the NATURALLEFTOUTERJOIN, why? — StackTrace
Please post an image of your data model diagram. DAX is highly dependent on it, and it's impossible to help without understanding tables and their relationships. — RADO
Also, please note that NATURALLEFTOUTERJOIN is very, very rarely used in DAX - it's simply not necessary if your data model is correct. Usually, it's usage is a symptom of an incorrect dimensional model. — RADO

Dan Dan · Accepted Answer · 2020-01-24T09:27:16

As others have commented, you should generally avoid using functions such as NATURALLEFTOUTERJOIN, as there are better ways to achieve the same thing, using a well-designed data model (star schema), where you can rely on explicit relationships between tables.

NATURALLEFTOUTERJOIN can only join two tables at a time. So if you absolutely want to use it this way, you'd have to nest it:

NATURALLEFTOUTERJOIN(
    NATURALLEFTOUTERJOIN(
        'Target',
        'Target_Category'
    ),
    'Target_Form'
)

But again - I want to emphasize that this is very bad DAX. It's much better to build a clean data model with proper relationships between these tables. Then, you can use a function such as SUMMARIZECOLUMNS to aggregate and query the data.

Also, in general, remember that DAX is best suited for aggregating data - if you want to query data at the individual record level, then a relational database such as SQL Server is a much better tool for you.

Error, Too many arguments were passed to the NATURALLEFTOUTERJOIN function. The maximum argument count for the function is

1 Answers