Forum Discussion

jkapso751's avatar
jkapso751
Icon for Helper I rankHelper I
3 years ago
Solved

CALCULATE with RELATEDTABLE

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 column...
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
sergej_og
Icon for Super User rankSuper User
3 years ago

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's avatar
    jkapso751
    Icon for Helper I rankHelper I
    3 years ago

    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.