cancel
Showing results 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

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

 Team GL account GL description Aggregation amount A 8000 Revenue domestic 1 500 A 8000 Revenue domestic 1 250 A 8100 Revenue forgein 2 200 A 8100 Revenue forgein 2 50 B 8000 Revenue domestic 1 125 B 8000 Revenue domestic 1 100 B 8100 Revenue forgein 2 300 B 8100 Revenue forgein 2 400

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)

Budget data

 Team GL account Aggregation budget A 8000 1 700 A 8100 2 275 B 8000 1 200 B 8100 2 700

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)

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

 Aggregation level description 1 domesic 2 foreign

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

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

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

 Aggregation revenue budget 1 750 700 2 250 275

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.

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
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.

3 REPLIES 3
Responsive Resident

Hi @Evandam

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

 Aggregation revenue budget 1 750 700 2 250 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!!

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.

Super User

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.

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors