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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Evandam
Helper II
Helper II

filter calcultated measure from table without relationship

Hi,

 

is was wondering if there is a solution for the following problem:

 

i have the following tables

 

dim team 

Team
A
B

 

Financial data (see below)

 

Budget data (see below)

==================

 

i have a table with financial data which i not already aggregated, and i have another table with budgets per GL account (so is already aggregated). I would like to "summarize" the financial data in a new created table using calculate and sum, but stille be able to use a slicer to filter the visual based on the selected team.

 

financial data

TeamGL accountGL descriptionAggregation amount
A8000Revenue domestic1500
A8000Revenue domestic1250
A8100Revenue forgein2200
A8100Revenue forgein250
B8000Revenue domestic1125
B8000Revenue domestic1100
B8100Revenue forgein2300
B8100Revenue forgein2400

 

via measure i calculate the sum for each aggregation level

Revenue_domestic = CALCULATE(SUM('Financial data'[amount]);'Financial data'[aggretation level]=1)
Revenue foreign = CALCULATE(SUM('Financial data'[amount]);'Financial data'[aggretation level]=2)
measure revenue.jpg
 
 

 

 

 

Budget data

TeamGL accountAggregationbudget
A80001700
A81002275
B80001200
B81002700

 

via measure i calculate the sum for each aggregation level:

Budget domestic = CALCULATE(SUM('Budget data'[Budget]); 'Budget data'[aggregation]=1)
Budget foreign = CALCULATE(SUM('Budget data'[Budget]);'Budget data'[aggregation]=2)
budget measures.jpg

 

no i make a new table via enter data. i define the colums: "aggregation level" and description.

Aggregation leveldescription
1domesic
2foreign

 

i then create a new column for revenue and budget and "assign" the measures to the proper row an IF statement:

Revenue = IF([Aggregation level]=1;[Revenue_domestic];[Revenue foreign])
Budget = IF('calculated table'[Aggregation level]=1;[Budget domestic];[Budget foreign])
calculated table.jpg

 

 

this table i will use in a matrix visual resulting in this overview:

matrix.jpg

 

No i want to add a slicer (from Team table) and for instance slice on team A. the data should than look like this

 

Aggregationrevenuebudget
1750700
2250275

 

the hard thing in my opinion is that when making a relationship to the financial table and budget table form the calculated table, the financial table and budget table are on the many side, whilst the calculated table is on the one side. 

model.jpg

 

creating a bidirectional relationship will only "work" for 1 table; fincial OR budget, so this is not a solution.

 

Is it possible to use a dax function to get te desired result?

1 ACCEPTED SOLUTION
mangaus1111
Solution Sage
Solution Sage

Hi @Evandam ,

you don´t need to create a calculated table, bidirectional relationships or something complicated like this.

See my pbi file and let me know if this is your expected result.

https://1drv.ms/u/s!Aj45jbu0mDVJi1-_MV3go4RnUpSZ?e=mawEr4

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

View solution in original post

3 REPLIES 3
Jayee
Responsive Resident
Responsive Resident

Hi @Evandam

 

If my understanding is correct you are expecting this table and filter by team.

Aggregationrevenuebudget
1750700
2250

275

 

In table visual take aggregation from calculated table, revenue from financial data and budget from budget table and take team from dim team table in slicer it will work perfectly.

 

If this post helps, then please consider Accept it as the solution, Appreciate your Kudos!!

mangaus1111
Solution Sage
Solution Sage

Hi @Evandam ,

you don´t need to create a calculated table, bidirectional relationships or something complicated like this.

See my pbi file and let me know if this is your expected result.

https://1drv.ms/u/s!Aj45jbu0mDVJi1-_MV3go4RnUpSZ?e=mawEr4

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

speedramps
Super User
Super User

 

We want to help you but your description is too vaugue. Please write it again clearly.

Please DON'T copy & paste your DAX and relationships that does not work and expect us to fathom what you want to do. That is a bit crazy. 😀

Please just give a simple non technical functional decscription of what you want, then let us suggest the DAX. Thank you.

Provide example input data as table text (not a screen print) so we can import the data to build a solution for you. Also provide the example desired output, with a clear description of the process flow.

 

Please click here to download an example PBIX 

Remember not to share private data ... we don't want you to get into trouble. 😧

Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.

You will get a quick response if you put time, care and effort into writing clear problem descriptions.

Vaugue descriptions can waste your time and ourtime.

Look foward to helping you when the above information is forthcoming

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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