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
AndreasSc
Helper I
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:

AndreasSc_0-1629284706844.png

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?

 

Thank you in advance!

 

Regards,

 

Andreas

4 REPLIES 4
v-eqin-msft
Community Support
Community Support

Hi @AndreasSc ,

 

Please try this:

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:

summarize type.jpg

 

For more details about DAX function reference, please follow the below official document:

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.

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

AndreasSc
Helper I
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.

m3tr01d
Continued Contributor
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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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