Forum Discussion

pcav's avatar
pcav
Helper I
7 years ago
Solved

Filter contexts with constants

In my never ending quest to understand and apply filter contexts I would be grateful if somebody could point me in the right direction

 

I have a set of engineers who either INSTALL (IN) or SERVICE (SV) equipment

 

 

I want to create a table visual in PowerBI to show the hours they worked compared against the "WorkingHours" which is effectively a global constant defined in a measure dynamically calculated from a date table "WorkingHours=(WorkingDays * HoursInWorkingDay)" as this varies each calendar month.

 

BUT I want to do this for INSTALL engineers.

 

When I create the PBI table visual with a fllter of INSTALL only it works as expected with the hours for the INSTALL engineers ONLY

 

 

BUT when I add in the measure for "working hours" the filters I have on the visual are ignored and I get ALL the engineers

 

 

What I want in is ONLY the INSTALL engineers with total working hours

 

 

I sort of understand why this happening as working hours has no relationship to the engineers specifically being a global constant but I don't understand how to get around it

 

Any help would be gratefully received.

 

 

 

 

 

Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi pcav ,

     

    Believe that you need to make changes on in order to force your working orders to get the IN from the enginners table try this code:

    WorkingHours IN =
IF (
    CALCULATE ( [Hours Booked]; Engineers[Discipline] = "IN" )
        = BLANK ();
    BLANK ();)
[Working Hours])

    In this measure I'm checking if the Booked hours are equal to IN if not will return blank for your measure, we are forcing the filter you added in the table visual for IN.

     

    Regards,

    MFelix

     

6 Replies

  • LivioLanzo's avatar
    LivioLanzo
    Solution Sage
    7 years ago

    Hello pcav ,

     

    one way for you to do this is to check if the hours booked returns a value and only then display the working hours

     

    WorkingHours = IF(ISBLANK(HOURSBOOKED), BLANK(), WORKINGHOURS)

    • pcav's avatar
      pcav
      Helper I
      7 years ago

      Thanks for your reply, but this doesn't solve the problem, I still get the "blank" rows for the Service engineer albeit the working hours field is blank.  

      • MFelix's avatar
        MFelix
        Super User
        7 years ago

        Hi pcav ,

         

        Believe that you need to make changes on in order to force your working orders to get the IN from the enginners table try this code:

        WorkingHours IN =
IF (
    CALCULATE ( [Hours Booked]; Engineers[Discipline] = "IN" )
        = BLANK ();
    BLANK ();)
[Working Hours])

        In this measure I'm checking if the Booked hours are equal to IN if not will return blank for your measure, we are forcing the filter you added in the table visual for IN.

         

        Regards,

        MFelix

         

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi pcav 

     

    One option would be to use a visual level filter and on Discipline-->IN

    A second option would be to create a slightly modified WorkingHours measure that is only non-blank when Discipline = "IN"

     

     

    WorkingHours_IN =
IF ( SELECTEDVALUE ( Table1[Discipline] ) = "IN", [WorkingHours] )

    This assumes  Table1[Engineer] in the rows of the matrix visual as you show. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi pcav 

     

    You have stated that WorkingHours comes from Date Table. How is your workedhours table structured?

     

    Can you post some sample data /  pbix in GoogleDrive or OneDrive and share the link here. This can help formulate a solution.

     

     

    Cheers

     

    CheenuSing