topic NATURALINNERJOIN function doesn't work matching a text column in DAX Commands and Tips

NATURALINNERJOIN function doesn't work matching a text column

Anonymous — Tue, 27 Apr 2021 13:26:02 GMT

Hi,

in a my pbi model I need to create a new table by DAX to match some columns of two tables created after more power query logics.

In order to accomplish a such task I've used the NATURALINNERJOIN function but unsuccessfully because I've obtained a cartesian product.

I've analyzed my data inside a bit test model and I've noticed that the issue is caused by the unmatching respect to a text column used in the join function. In particular, in the test model I've created manually two tables:

- Tab_A, with the text column named "Code" and an integer column named "obs_value",

- Tab_B, with the text column named "Code" and an integer column named "exp_value".

I've filled the column "Code" of these two tables with a sample value, "aaaa", adding 2 rows for Tab_A and 3 rows for Tab_B.
No relationships exist between Tab_A and Tab_B.

To create the new dax table I've used this function:

TAB_B_A_JOIN =
NATURALINNERJOIN
( SELECTCOLUMNS(Tab_B,
"Code", Tab_B[Code]&"",
"Expected value", Tab_B[exp_value]+0 ),
SELECTCOLUMNS(Tab_A,
"Code", Tab_A[Code]&"",
"Observed value", Tab_A[obs_value]+0 )
)

but it has returned 6 rows and not 2 ones.
With an integer column as a matching column, the above dax expression functions with success!

I think to use a whatever transformation to obtain a numeric value from the text column, but I don't know a such dax function.

Other suggests to me in order to solve this issue, please? Thanks 🙂

Re: NATURALINNERJOIN function doesn't work matching a text column

dedelman_clng — Thu, 29 Apr 2021 14:29:20 GMT

Hi Anonymous -

I cannot replicate your results. Perhaps I don't have the correct data in Tab_A and Tab_B?

Tab_A

Codeobs_value

aaaa	1
bbbb	2

Tab_B

Codeexp_value

aaaa	3
bbbb	4
cccc	5

TAB_B_A_JOIN (copied your code directly from the post)

CodeExpected valueObserved value

aaaa	3	1
bbbb	4	2

If that is not the correct source data, please clarify. Perhaps there are relationships you're not aware of that may be affecting your results?

Hope this helps

David

Re: NATURALINNERJOIN function doesn't work matching a text column

Anonymous — Thu, 29 Apr 2021 16:42:24 GMT

Hi, thanks for your reply.

I've discovered some errors in data for Tab_A and Tab_B, sorry.

It seems that the NATURALINNERJOIN functions rightly as you have indicated.

Many thanks and bye