Forum Discussion

yoyfreddy's avatar
yoyfreddy
Frequent Visitor
4 years ago
Solved

Visual with multiple filter not working

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 !

Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    4 years ago

    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.

2 Replies

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    4 years ago

    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's avatar
      yoyfreddy
      Frequent Visitor
      4 years ago

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