March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to Solution.
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:
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCan 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 Forum
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingFor 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:
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingthanks you this is it!! very nice solution
Great @Anonymous . Glad you have a working solution now.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHI @Anonymous,
You can test to use this calculate column formula to lookup corresponds 'Account HYP' based on its account range:
Accoun HYP =
CALCULATE (
MAX ( 'dimension'[Accoun HYP] ),
FILTER (
ALL ( 'dimension' ),
'dimension'[Account iniciate] <= EARLIER ( 'information'[Account] )
&& 'dimension'[Account Finish] >= EARLIER ( 'information'[Account] )
)
)
Regards,
Xiaoxin Sheng
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
90 | |
86 | |
76 | |
49 |
User | Count |
---|---|
167 | |
149 | |
99 | |
73 | |
57 |