Forum Discussion

sam_gift's avatar
sam_gift
Icon for Helper I rankHelper I
2 years ago

DAX to create columns using userelationship

 Hi Have 2 tables. Customer and Fact

Customer:

Fact:

 

They have 1 active and 2 inactive relationships.

 

How can I create a table with all the Payer, Billed and shipped customers information with revenue.

I have to use only dax not power query.

Please help

 

1 Reply

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    2 years ago

    hi sam_gift ,

     

    not sure if i fully get you, you may plot a table visual with customer[customer_key] with three measures like:

    RevenueByPay = SUM(fact[Revenue])

RevenueByBill = 
CALCULATE(
    [RevenueByPay],
    USERELATIONSHIP(fact[BillToCustomerKey], customer[customer_key])
)

RevenueByShip = 
CALCULATE(
    [RevenueByPay],
    USERELATIONSHIP(fact[ShipToCustomerKey], customer[customer_key])
)

     

    it worked like: