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

    Hello @vengadeshpalani,

    The Measure I created provides the behavior you're seeing,

    see screenshot below, link to the sample pbix file shows pbix

    richbenmintz_0-1596631485276.png

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!