Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
trev1972a
Helper I
Helper I

Cant join two custom columns

Hi All

 

Ive been using BI for a week now, after a few initial issues i got a few useful visualisations done, and have been asked to do some minor changes

 

I cant seem to join two custom columns however- i get the error 'cardinality is not valid' and it will only allow me to do a many to many relationship which is no use.

 

The two tables are salesorders, and traders.

 

Salesorders contains the fields 'traderid' and 'tradertype'

The traders table contains the fields 'id' and 'tradertype'

 

so i have created custom colmns on both queries combining both values (see attached)

 

Unfortunately when i try to join them, i get the attached issue, i have tried removing blanks from both queries, and removing duplicates on that column for the traders table (although duplicates should not be possible)

 

Any ideas?

 

Thanks

joinerror.jpg

 

1 ACCEPTED SOLUTION

Please make sure you are recasing before removing duplicates.

If you are doing so, trim won't actually impact - DAX wouldn't consider them duplicates.

On the other hand, unseen white space (I don't know how you are combining the columns) might be stripped when loaded to desktop engine, you can try Text.Clean prior to the call to Distinct/Group.

 

Another thing you can try is to eyeball the difference: add a calculated column in DAX akin to

Column = 
   var v = [traderidandtype] 
   return CALCULATE( COUNTROWS( salesorders), salesorders[traderidandtype] = v ) > 1

and just 2 values will be TRUE

 

View solution in original post

8 REPLIES 8
amitchandak
Super User
Super User

Can you check there is no duplicate for this column traders table?

In table or matrix put that column and put the count of that column sort desc. There should not be any duplicate. If there is then work to make it unique

 

Hi 

 

Count of the custom column is not showing duplicates , count for every row shows '1' 

Thankscount.jpg

 

 

 

Can I assume you are trying to create a relationship as the screenshot indicates, and not do a "Join" as the thread subject suggests?

mquery grouped count does not assure unique values in desktop, because of case sensitivity.

So, id "1234,a" and "1234,A" might both show a count of 1, but DAX considers them duplicate strings.

What do you see if you view table in desktop "Data" view?

 

unique.png

If that distinct count does not equal row count, you have identified your issue.

If case does not matter to you, transform to upper or lower prior to Table.Distinct or Table.Group and you can create the 1-* relationship

 

Ahhh we are getting somewhere!! Thanks!

I see 9618 rows, but only 9617 distinct

 

However i have removed duplicates, uppercased and trimmed and i still have that one duplicate there.

 

Hi @trev1972a ,

 

Maybe you can use "Replace Value" feature, like this:

hh4.PNG

hh6.PNG

hh7.PNG

This will replace all lowercase letters in the column with uppercase.

 

Best regards,
Lionel Chen

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

Please make sure you are recasing before removing duplicates.

If you are doing so, trim won't actually impact - DAX wouldn't consider them duplicates.

On the other hand, unseen white space (I don't know how you are combining the columns) might be stripped when loaded to desktop engine, you can try Text.Clean prior to the call to Distinct/Group.

 

Another thing you can try is to eyeball the difference: add a calculated column in DAX akin to

Column = 
   var v = [traderidandtype] 
   return CALCULATE( COUNTROWS( salesorders), salesorders[traderidandtype] = v ) > 1

and just 2 values will be TRUE

 

Thank you SO much, removing duplicates after the uppercase got rid of it and i have been able to do the one to many relationship (i had previously got remove duplicates before my uppercase step)

 

I am really grateful all, it is a learning process for me as i have been using it a week but i hope to be as helpful as you all to new members in a few months 🙂 

Hi @trev1972a ,

 

I tested every possibility but didn't find your problem, would you mind analyzing your .pbix file?

 

Best regards,
Lionel Chen

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.