cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vengadeshpalani
Helper III
Helper III

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

 

 

Screenshot_10.png

1 ACCEPTED SOLUTION

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
v-lionel-msft
Community Support
Community Support

Hi @vengadeshpalani ,

 

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])
    )
) 

 

v-lionel-msft_0-1596594583271.png

 

 

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.

 

 
 

 

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

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

mhossain
Super User I
Super User I

@vengadeshpalani 

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

Hi @vengadeshpalani ,

 

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()
)

 

 

richbenmintz_1-1596574550649.png

 

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!

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




SergioTorrinha
Resolver I
Resolver I

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 🙂

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors