Forum Discussion
Problem with Dimension relationship
Hello i need some help about a new account base that i am doing.
I have a base off accounting and in the same time i have a full dimensional tables where the account flexfield correspond another account.
For example the account flex 702027005000 correspond an acount in a list that i have, but the poblems is that on the table tell me that the account that go between 702027001000 to 702027500000 there is a hyp account R400200 but 702027500000 to 702027999000 is a HYP account R400500. So the problems is that i don't know how to build the relations in this cases. Threre are others that there easy because one flex have one HYP Account, but i don´t know hov to do it in that cases.
Somebody knows how to build this kinds of relationships?
Regards
Yes. Simply merge the data in Power Query, then do some filtering on nested tables. You'll get this result - Note: Once you load this, the account number will look fine depending on the format. Power Query uses scientific notation because it is such a large number:
You can see the steps in the PBIX file linked to here. But what I did was:
- Imported both of your tables into Power Query
- Made sure the Dimension table is not set to load into Power BI's DAX model
- Split the account number based on the '/' delimiter
- In the base table, added the entire Dimension Table as a nested table for each record
- Selected only the records where the account field is between the first and second account
- Expanded the HYP account, then removed unnecessary columns.
Let me know if that helps. For future reference on pasting data in here for us to use, please see this URL. Your data above has a ton of spaces and other characters that I spent more time cleaning up than I did working on the actual solution. 🙂
How to provide sample data in the Power BI Forum
6 Replies
Can you be a bit more clear on what you are asking? I am a CPA and I don't know what a HYP Account is nor do I know what "base off accounting" is. I understand you are trying to build relationships between tables, but I cannot understand what the tables are. See links below:
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI ForumFor example, in my base of information i have:
Account Import Account HYP
702027005000 $ 100.000 ?
702027503000 $ 500.000 ?
In my table of dimension i have:
Accoun HYP Account Between Account iniciate Account Finish
R400200 702027001000/702027500000 702027001000 702027500000
R400300 702027500001/702027999999 702027500001 702027999999
I need to build a relationship between the base and the dimension where the base can complete the necesari information like:
Account Import Account HYP
702027005000 $ 100.000 R400200
702027503000 $ 500.000 R400300
Is that posible?
Yes. Simply merge the data in Power Query, then do some filtering on nested tables. You'll get this result - Note: Once you load this, the account number will look fine depending on the format. Power Query uses scientific notation because it is such a large number:
You can see the steps in the PBIX file linked to here. But what I did was:
- Imported both of your tables into Power Query
- Made sure the Dimension table is not set to load into Power BI's DAX model
- Split the account number based on the '/' delimiter
- In the base table, added the entire Dimension Table as a nested table for each record
- Selected only the records where the account field is between the first and second account
- Expanded the HYP account, then removed unnecessary columns.
Let me know if that helps. For future reference on pasting data in here for us to use, please see this URL. Your data above has a ton of spaces and other characters that I spent more time cleaning up than I did working on the actual solution. 🙂
How to provide sample data in the Power BI Forum