Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey fellow users of Power Bi,
First of all I wouldnt be here if I hadnt searched and tried figuring this out by my self at first, but sadly without any luck.
My situation will be a bit hard to explain, and english isnt my native language, so bare with me and please dont hold back if you need further explaning or have any questions, I will of course do my best to answer.
I have two "Imports" with advanced options:
Each import uses the two same tables, but with different SQL statements and they go as follows:
"Import" A:
Select *
from D, K
where K.DID = D.dDID and d_type = 1 and D.Own<> 'You' and D.Dub<> '2' and EntryDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0) and (d_date > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0) or d_date is null)
"Import" B:
SELECT *
FROM D, K
Where K.DID = D.dDID
AND dte_type = 2 AND D.Own<> 'You' and D.Dub<> '2' and EntryDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0) and (d_date > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0) or d_date is null)
These two "Imports" doesnt have anything really unique in em and therefor I have made a new column in each "import" called "SKey" based on two other colums as follows:
=[d_ven] & "|" & [Field2] as SKey
This isnt unique either but gets quite close, so now I need to make a many to many relationship.
I have looked up on the internet and created a bridge table by right clicking Skey in each "Import" and pressed "Add as new query", made em both into a table and appended them with eachother, after that I removed all the dublicates:
Now I have tried to build the relationship from the bridged table to one of the two "Imported table" but it STILL pops up with the following error message:
How can this be, I have spent hours on trying to figure out how I can build a many to many relationship between these two imports, but sadly without any luck.
I hope one of you out there could be so kind and try to help me out here, as I am growing grey hair earlier than I should.
Best regards Henrik V.
PS: Once again, sorry for my bad english, but as said it aint my native language, so just let me know if you need any questions answered.
Solved! Go to Solution.
Found a solution, problem was bugs regarding duplicates in power bi
Have you got any blanks in your data?
Proud to be a Super User!
If you want to I would really like to purchase 1-2 hours of help on how to setup my relationships correctly, then you can see my entire structure.. over skype?
There is also one other thing you can do to isolate the problem
in your visual layer on both sides of the relationships
place 2 tables on the visual desktop canvas
and drag the key field from either side of the tables into their own table - drag the key field in again and change it to a count, so that it aggregates on the key field.
and then sort by the count field, in descending order you should see the cullprits on the top.
let me know if you need a visual
Proud to be a Super User!
Sorry, I am quite new to power bi in general and would love to see it visually if possible or perhaps a skype session tomorrow? As said I really do not mind paying in order to get all my relationships working=/
Lol, its late here i am heading for bed,
the only other advice i would give you is
1. In power query under the transform button do format / trim and clean on all fields that you use to make up your key before you make up the key
2. Power query is case sensitive, if you have different cases it will see it as different so de deduplicating wont help because it will see it as unique, however dax sees lower and upper case as the same, so transform your keys into uppper or lower and then only remove duplicates after you have done the above.
3. As i said previously any blanks or null values will also create issues so you will need to pad them with other values ie. replace with -1 or something.
Hope that helps.
Proud to be a Super User!
I tried doing it a bit different, and now I can create some relationships as seen here:
but if I try drag a column in from each table it says it cant display the visual
And if you click on see details, what does it say?
Proud to be a Super User!
Found a solution, problem was bugs regarding duplicates in power bi
Hi @Shamatix,
Could you post your table structures with some sample data which can reproduce the issue, so that we can help further investigate on it? It's better to share a sample pbix file if possible. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Regards
No I do not =/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |