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 I

## Summarization with related tables

Hi together,

I have the following data-modell where I turned some n:m relationships into 1:n relationships with a relationship table:

The basic idea is, that a person has a numeric effort during a specific timeline.

Now I want to summarize all efforts from all persons.

I think I need a DAX-function for this, right? (I'm not familiar with DAX, sorry)

Which DAX-function do I need to make a summarization within related tables?

Regards,

Andreas

4 REPLIES 4
Community Support

Hi @AndreasSc ,

``avg = CALCULATE(AVERAGE(Effort[Effort_value]), ALLEXCEPT(Person,Person[ID_Person]),ALLEXCEPT('Time','Time'[ID_Time]))``

Or change the default summarization type to Average like this:

Data Analysis Expressions (DAX) Reference - DAX | Microsoft Docs

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

Helper I

Hi @v-eqin-msft ,

unfortunately this messure doesn't work for me. I get some strange numbers.

Do you use the Primary Keys from the related tables within the ALLEXCEPT statement, right?

Regards,

Andreas

Helper I

Thank you very much for this hint! It works with SUM now.

But I have another question regarding this topic:
My values are on a monthly basis.
If a user selects two months for example, the values are just summarized but actually it should calculate the average of both monts.

How do I determine the selected months to be able to do this calculation? Is there a DAX-function for it?

Thank you again.

Continued Contributor

Hi,

I think you should let the Effort_Value denormalized in the table Person/Effort table. This way, you will be able to do just a regular SUM of the effort_value column

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 - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors