Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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 amitchandak
Count of the custom column is not showing duplicates , count for every row shows '1'
Thanks
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?
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:
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.
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |