Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Need Help
  • edhans's avatar
    edhans
    6 years ago

    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:

    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

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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?

 

   

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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