Filter contexts with constants

[pcav]

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.

[CheenuSing]

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

[AlB]

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. 

[LivioLanzo]

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]

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 · Accepted solution]

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

[pcav]

Thanks very much for taking the time to reply.

This works just great and is MUCH better that the solution I came up with

Regards