cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Block relational filter

Hello everyone,

 

How can I use the calculation of a measure without considering the filter of the relationship column between tables?

 

I have these two tables, Table Employee and Table Calendar.

 

1.JPG

Relationship:   Day Worked ------ Date

 

The Day Worked column contains only the days the employee worked

The Date column contains all days of the year.

The column Business day considers 1 being (Monday to Friday) and 0 being (Saturday and Sunday).

 

Using the Dax measure below, I can count the working days for each period of time, but I would like this value to be locked for each filtered employee.

 

Business day by Employee = CALCULATE(COUNT('Table Calendar'[Date]); 'Table Calendar'[Business day] = 1)

 

2.JPG

It is possible?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Hi @XTF_2020 ,

 

Use the all formula on the calculation:

 

 

 

Business day by Employee = COUNTROWS(FILTER(ALLSELECTED('Table Calendar'); 'Table Calendar'[Business day] = 1))

 

 

Not on computer did this by heart may need some changes.

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

4 REPLIES 4
Super User III
Super User III

Hi @XTF_2020 ,

 

Use the all formula on the calculation:

 

 

 

Business day by Employee = COUNTROWS(FILTER(ALLSELECTED('Table Calendar'); 'Table Calendar'[Business day] = 1))

 

 

Not on computer did this by heart may need some changes.

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Super User II
Super User II

Hi @XTF_2020 ,

 

Try modifying your dax as follows:

 

Business day by Employee = CALCULATE(COUNT('Table Calendar'[Date]); FILTER(ALL('Table Calendar'), 'Table Calendar'[Business day] = 1))

 

Thanks,

Pragati





If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Super User I
Super User I

@XTF_2020  Try this:

Business day by Employee =
CALCULATE (
    COUNT ( 'Table Calendar'[Date] ),
    'Table Calendar'[Business day] = 1,
    ALL ( TableEmploye )
)

Thank you,
Antriksh Sharma
Super User IV
Super User IV

@XTF_2020 , if business day value is 1 and 0

the sum will give what you have in actual and the count will give what you have desired output



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors