Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 🙂
Solved! Go to Solution.
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
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
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
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |