Problem with Dimension relationship

[Anonymous]

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

[edhans]

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

[Anonymous]

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?

[edhans · 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:

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]

thanks you this is it!! very nice solution

[edhans]

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

[v-shex-msft]

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