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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

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

1 ACCEPTED 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:

2020-03-24 08_08_28-Untitled - Power Query Editor.png

 

You can see the steps in the PBIX file linked to here. But what I did was:

  1. Imported both of your tables into Power Query
  2. Made sure the Dimension table is not set to load into Power BI's DAX model
  3. Split the account number based on the '/' delimiter
  4. In the base table, added the entire Dimension Table as a nested table for each record
  5. Selected only the records where the account field is between the first and second account
  6. 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

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

6 REPLIES 6
edhans
Super User
Super User

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 Forum



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

For 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:

2020-03-24 08_08_28-Untitled - Power Query Editor.png

 

You can see the steps in the PBIX file linked to here. But what I did was:

  1. Imported both of your tables into Power Query
  2. Made sure the Dimension table is not set to load into Power BI's DAX model
  3. Split the account number based on the '/' delimiter
  4. In the base table, added the entire Dimension Table as a nested table for each record
  5. Selected only the records where the account field is between the first and second account
  6. 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

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

thanks you this is it!! very nice solution

Great @Anonymous . Glad you have a working solution now.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

HI @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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.