Forum Discussion

RobX's avatar
RobX
Icon for Advocate I rankAdvocate I
5 years ago

Multiply with Multiple Where Clause

My model contains 3 tables that are related to each other.  Relationship is "Table Cost to Table Lead on Source" and "Table Lead to Table Sale on Lead".

 

I need to be able to create a measure that multiplies the count of sales by source against the cost of each source where the date of the cost is the same as the date of the sale.

 

I tried CALCULATE(COUNT(Sale[Lead] * Sum(Cost[Cost]), Sale[SaleDate] = Cost[Date]) but that doesn't work.

 

 

End result would be this.

 

 

2 Replies

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion
    5 years ago

    How much data will you have?  Frankly I think everything would be easier if you simply merge the data in Power Query prior to load. You can merge the cost and lead table with 2 joins (date and source) and then extract the relevant cost into the lead table. I would then merge the sale date into the lead table too. Then the DAX is easy

    • RobX's avatar
      RobX
      Icon for Advocate I rankAdvocate I
      5 years ago

      Matt,

       

      I was hoping to avoid merging the tables.

       

      Prey costs would coincide with the sale date so I'd have to bring the source column into the sales table and then merge that way, which isn't ideal since I lost the ability to project costs out to the leads.