cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pcav Frequent Visitor
Frequent Visitor

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

 

cv1.PNG

 

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

 

Cv2.PNG

 

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

 

Cv3.PNG

 

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

 

Cv4.PNG

 

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.

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Filter contexts with constants

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 ()Smiley Wink
[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

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




6 REPLIES 6
Super User
Super User

Re: Filter contexts with constants

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)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Super User
Super User

Re: Filter contexts with constants

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. 

Super User
Super User

Re: Filter contexts with constants

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
pcav Frequent Visitor
Frequent Visitor

Re: Filter contexts with constants

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.  

Super User
Super User

Re: Filter contexts with constants

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 ()Smiley Wink
[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

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




pcav Frequent Visitor
Frequent Visitor

Re: Filter contexts with constants

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