issue with filter context formula

[YBZ]

Hi all,

I am getting in the topic of filter context and I would like to create an easy formula in my report that filters based on values in another table.Somehow however it doesn't work and therefore I created an example sample with 2 Tables. 

I would need to sum up the 'hours' in table 1 based on the department filter. Hence, result would need to be 4

Could you please support? That would be much appreciated!

Regards

[v-yalanwu-msft]

Hi, @YBZ ;

Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we could close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,

Community Support Team_ Yalan Wu

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

[v-yalanwu-msft]

Hi, @YBZ ;

Try it,

SumHours =
CALCULATE (
    SUM ( Table1[hours] ),
    FILTER (
        Table1,
        Table1[department] IN ALLSELECTED ( TableSelectedDepartment[department] )
    )
)

Or

SumHours =
CALCULATE (
    SUM ( Table1[hours] ),
    FILTER (
        Table1,
        Table1[department] = MAX ( TableSelectedDepartment[department] )))

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[David-Ganor]

Hi @YBZ 

First of all, you can create a realtionship between the tables - with direction from TableSelectedDepartment to 1,  So basically TableSelectedDepartment  acts like a dimension.

If that somhow not possible you can create the following measure:

SumHours=

Calaculate(Sum(Table1[hours])

,FILTER(Table1

,Table1[department]=TableSelectedDepartment[department]))

Hope it helps,

David Ganor

[YBZ]

Thanks, I would indeed a measure as I made the connection in my database too difficult... 🙂 

I understand the logic behind it, but when I try it in PowerBI, it seems that PowerBI only allows me to put a measure for TableSelectedDepartment[department]).

SumHours=

Calaculate(Sum(Table1[hours])

,FILTER(Table1

,Table1[department]=TableSelectedDepartment[department]))