Forum Discussion

Lightice83's avatar
Lightice83
Helper I
3 years ago
Solved

Reference fact/data table from Dimension table

Hi,

 

I'm doing the Mavern Analytics advanced DAX course & there is a example where we create a calculated column in the Fact/Lookup table for Customers to return total revenue (for all customers) from the Sales table Fact/Data table (this is being used as a input to a bigger calculation) into the Dimension/Lookup table

 

The calculation used is SUMX (see below) but would this not calculate the revenue per customer not all customers?  Or is it because there has been no context transition (starting with row context & not used calculate) hence it is just doing the SUMX over every customer?

 

Thanks

 

Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    Lightice83,

     

    The SUMX in the example would return the same amount for each customer, because it is iterating the entire fact table without regard to the relationship between Customers and Sales by Store. However, if you add RELATEDTABLE it will filter the fact table per the relationship with Customers:

     

    SUMX (
    RELATEDTABLE ( 'Sales by Store' ),
    'Sales by Store'[quantity_sold] * 'Sales by Store'[unit_price]
)

     

1 Reply

  • DataInsights's avatar
    DataInsights
    Super User
    3 years ago

    Lightice83,

     

    The SUMX in the example would return the same amount for each customer, because it is iterating the entire fact table without regard to the relationship between Customers and Sales by Store. However, if you add RELATEDTABLE it will filter the fact table per the relationship with Customers:

     

    SUMX (
    RELATEDTABLE ( 'Sales by Store' ),
    'Sales by Store'[quantity_sold] * 'Sales by Store'[unit_price]
)