Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Conditional Joining two Table

Hi ALl,

 

I have Two Table : Account No ,  Account Category

 

I want to connect these two table by Accoun Number

 

Ex: Account No : 11042-000 is want categorized Under Total Assets & Tatal Cash. Because Account No From 10001-000 to 19990-000 want to categorized Under Total Assets &  From 11000-000 to 11990-000 want to categorized Under Total Cash.

 

11042-000 is satisfied these two Condition  

 

Pbix File: https://www.dropbox.com/s/c8ca1a4d85vn6p6/Account%20Category.pbix?dl=0

 

 

Need Help

6 Replies

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Don't create table relationships, just write a measure.

     

    __amount = 
CALCULATE(
    SUM('Account No'[Amount]),
    FILTER(
        'Account No',
        'Account No'[Account No] >= MAX('Account Category'[Account No Start From]) && 'Account No'[Account No] <= MAX('Account Category'[Account No End To])
    )
)

     

     

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     
     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks For your Reply,


      If i Click Total Assets, I want to filter out the Account No Related to Total Assets Category


      I want to see the detail level also.

       

      Can you Please Help me to resolve this logic

       

  • SergioTorrinha's avatar
    SergioTorrinha
    Resolver II
    6 years ago

    Hi!
    I belive you need some sort of "bridge" table mate. 🙂
    Just:

    1. In Power Query build a table with 5 fields: [Account No], [Account No Start From], [Account No Start To], [Key] = [Account No Start From] + [Account No Start To], where + represents string concatenation between 2 columns (you can also use SQL if your tables come from a sql data source)
    2. build a [key] column in your Account category table

    3. Connect the bridge table buit in 1 to the Account Category table by the [Key] field

    4. Connect the bridge table buit in 1 to the Account No table by the [Account no] field
    5. Use the fields from your bridge table to make the reports


    Hope this helps 🙂

  • mhossain's avatar
    mhossain
    Solution Sage
    6 years ago

    Anonymous 

    Seems you require to maintain and create a separate mapping table and create relationship to existing two tables.

    • richbenmintz's avatar
      richbenmintz
      Resident Rockstar
      6 years ago

      Hi Anonymous ,

       

      I am going to attack this a little differently as this is a chart of accounts and the aggregation is not really additive, below you will find a measure that sums up the values of the transactions based on the range in the Account Category Table

       

       

      Balance Measure = 
if(
    HASONEVALUE('Account Category'[Account Category]), 
    CALCULATE(sum('Account No'[Amount]), FILTER('Account No', 'Account No'[Account No] >= max('Account Category'[Account No Start From]) && 'Account No'[Account No] <= MAX('Account Category'[Account No End To])))
    ,Blank()
)

       

       

       

      Hope this helps,

      Richard

       


      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!