CODEWITHSUNDEEP
ssaspowerbidaxssas-tabular
Søren Kongstad
Søren Kongstad

Reputation: 1440

Calculating filtered cost

(Edited for clarity) I am new to DAX and tabular, and have run into a problem

We have a tabular model with multiple fact tables, sharing some dimensions.

Now our challenge is that cost for one fact, should be calculated on the basis of cost on another table, using a order relationship.

The idea is that the DBM cost is the correct one, but the cost in Adform might be from some other channel, and in that case we should use the cost from Adform.

So we need to check if there is an associated DBM cost related by orders.

We have:

Adform Fact

Adform ID, Order Id, Impressions, Cost
1 , 1 , 100 , 50
2 , 2 , 200 , 68
3 , 2 , 200 , 100
4 , 3 , 200 , 100
5 , -1 , 600 , 300

DBM Fact

DBM ID, Order Id, Impressions, Cost
1 , 1 , 50 , 25
2 , 1 , 20 , 10
3 , 1 , 10 , 10
4 , 2 , 60 , 100
5 , 2 , 80 , 75
6 , -1 , 500 , 1000

And Order dimension

Order Id, Order Name
1 , "Campaign 1"
2 , "Campaign 2"
3 , "Campaign 3"
-1 , "Unknown Order"

Now we need to do the following.

Cost has to be taken from the Adform Cost, unless the same order has associated cost in the DBM table. In this case Cost should be calculated as the sum of the cost from DBM filtered on common dimensions.

I've tried this:

CALCULATE(if(and(COUNTROWS('AdForm')>0,[Cost (DBM)]>0) ,[Cost (DBM)],[Cost (AdForm)]))

This works as expected when I drill down on orders. However it only does this on the aggregated level, so when I drill down on other common dimensions such as Date, or Client, I simply get the sum of DBM cost as the cost.

What I would like to get is the sum DBM Cost of all orders where there is a DBM Cost, and for all other orders, to get the Adform Cost.

Upvotes: 2

Views: 117

Answers (1)

Mike Honey
Mike Honey

Reputation: 15037

Tight spots can be uncomfortable, but remember that diamonds are only made under extreme pressure...

I would first add Measures:

Adform Cost = SUM ( 'Adform Fact'[Cost] ) 

DBM Cost = SUM ( 'DBM Fact'[Cost] )

Then I would use SUMX to run a similar (but simplified) calc on a row-by-row basis across the Orders table:

Order Cost =
SUMX (
    'Order dimension',
    IF ( ISBLANK ( [DBM Cost] ), [Adform Cost], [DBM Cost] )
)

Upvotes: 1

Related Questions