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) ...
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]
)

     

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]
)