Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hello @vengadeshpalani,
The Measure I created provides the behavior you're seeing,
see screenshot below, link to the sample pbix file shows pbix
Proud to be a Super User!
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.
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
Proud to be a Super User!
@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()
)
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!
Proud to be a Super User!
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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |