Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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



I hope this helps,
Richard

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

Proud to be a Super User!


View solution in original post

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

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

 

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.

 

 
 

 

Anonymous
Not applicable

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



I hope this helps,
Richard

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

Proud to be a Super User!


mhossain
Solution Sage
Solution Sage

@Anonymous 

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

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

 

 

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!

 

 



I hope this helps,
Richard

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

Proud to be a Super User!


SergioTorrinha
Resolver II
Resolver II

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.