CALCULATE with RELATEDTABLE

[jkapso751]

Hi all,

I have a hopefully quick question.  Here is the kind of data I'm working with: tables DATA and DATA1.

There is a one-to-many relationship between DATA1 and DATA company code columns.  I created the following measure:

Related1 = CALCULATE(sum(DATA1[Revenue]),RELATEDTABLE(DATA))

Bringing the measure into the visual I get the following (which is exactly what I wanted):

Although the measure is giving me what I want, I'd like to understand the logic behind how this works.  If anyone can send me an explanation of the logic behind the DAX formula here, it would be fantastic.

Many thanks!

John 

 

[jkapso751]

Hi v-binbinyu,

Thanks for your explanation!  Much appreciated!

The reason I used this formula was to avoid having to create a calculated column in DATA (where I would've inserted a RELATED formula to bring in the related revenue from DATA1).  In your opinion, is this a more efficient approach?  Or would the calculated column + RELATED be the better alternative?

Best regards,

John

[v-binbinyu-msft · Accepted solution]

Hi @jkapso751 ,

This DAX formula is using the CALCULATE function to calculate the sum of the Revenue column in the DATA1 table. The RELATEDTABLE function is used to retrieve the related table (DATA) based on a relationship between the two tables.

The RELATEDTABLE function returns a table that is related to the current table in the filter context. In this case, the filter context is determined by the rows in the table where the formula is being evaluated.

The formula is essentially saying "calculate the sum of Revenue from the DATA1 table, but only for the rows in the related DATA table that are currently in the filter context."

So, for each row in the table where the formula is being evaluated, the formula will sum up the Revenue column from the related DATA1 table, based on the relationship between the two tables and the current filter context.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sergej_og]

Hi @jkapso751 ,
maybe you can get more information from here:
https://dax.guide/relatedtable/

Because you have a 1:n relationship between your tables, maybe you can drag&drop Revenue-field from DATA1 in your visual... if it meets your needs.
If you would like enrich yout table DATA with data from DATA1 you can use RELATED.

Regards

[jkapso751]

Thanks sergej_og.

I'm actually trying to create a measure that uses Related1 as an input.  I'd like to do that without using a calculated column to bring the DATA1 revenue into the DATA table (via RELATED function).  So it seems I found a way to do that, but I just want to understand how it works (or learn a better way if there is one).

As I understand it, RELATEDTABLE is returning a table with all the related rows (which are the rows from DATA with the same company code).  This table is then used as a filter argument for the CALCULATE expression.  I'm just not sure how to explain what happens next.