Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

issue with filter context formula

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

 

 

 

 

Need Help
Show and Tell

4 Replies

  • David-Ganor's avatar
    David-Ganor
    Resolver II
    4 years ago

    Hi Anonymous 

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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]))

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, Anonymous ;

    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.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, Anonymous ;

    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.