Visual with multiple filter not working

[yoyfreddy]

Hello experts!

I have a table containing the Unit Cost of different Cost Category of different Airlines; and another table with the Cost Driver of different Cost Category of different Airlines. I wish to show Total Cost by airline and by cost category in a single visual.

But, I cannot create two active relationships across two tables, so the visual cannot distinguish each Airline's Cost Driver.

   My expected result:

   The actaul result:

   Source data : Extract of Unit Cost Table:

  Source data :  Extract of OpStat (Cost Driver) Table

Relationship Table

Here is my measure:

Unit Cost in period = Cost[Unit Cost]* CALCULATE(sum(OpStat[Value]),

DATESBETWEEN('Date'[Date].[Date],FIRSTDATE('Date'[Date]),LASTDATE('Date'[Date])))

(My measure have another bug related to the Date measure, please ignore it, that will be a seperate question.)

My BI Dashboard and Data File can be retrieved here :
test - Copy

Thanks a lot !

[V-lianl-msft · Accepted solution]

Hi @yoyfreddy ,

Create a new column in Cost table:

Driver = RELATED(Classification[Cost Driver])

Create a new measure like below:

Measure = 
var unit_cost = MAX(Cost[Unit Cost])
var airline_ = MAX(Cost[Airline])
var driver_ = MAX(Cost[Driver])
var sum_opstat = CALCULATE(SUM(OpStat[Value]),FILTER(OpStat,OpStat[Airline]=airline_&&OpStat[Cost Driver]=driver_))
return MAX(Cost[Unit Cost])*sum_opstat

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yoyfreddy]

Thank you Liang for your help. Your file also solved my Date problem too!