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 🙂
