cancel
Showing results for
Did you mean:
Highlighted
palvarez83 Regular Visitor

## Help with dax filter function using measures as arguments.

Hello,

I was hoping someone can help me figure out my syntax problem.  I have two tables disCalendar and fCommTime which are not relate.  My disCalendar table has 3 useful columns I am trying to use to filter:

1.  disCalendar[Date] which is every date from 1/1/18 until 1 year from today.

2.  disCalendar[Workday] which is either a 1 or 0 dpending on if it is true/false

2.  disCalendar[Productivity] which is a factor can either be 1.0, 1.4, 1.6, or 1.8 depending on the date.

I would like to create a couple of measures to:

1. Find the next working day given the takes a given date from one of the fCommTime Columns and

2.  Lookup the productivity on that specific date from disCalendar

Heres what I have so far:

1.

`[End date]=SUM(fCommTime[ActualEnd])`

This summarizes the date in rows from the given date column and returns the correct date based on filter context.

2.

`[NextWkDy]=calculate(min(dCalendar[Date]),filter(dCalendar,dCalendar[Date]> [End date] && dCalendar[Workday]=1))`

This uses the filter function to filter dCalendar table to allow only dates that come after [End date] and are a working day (dCalendard[workday]]=1.  Then it returns the earliest date in that filtered table using the min(0 function.  It appears to be working fine up until then.

Now the problem.  I would like to lookup the productivty for the next work day given from the previous measure.  Here is what I tried.

`[NextWkdyProd]=CALCULATE(Sum(dCalendar[Productivity]),filter(dCalendar, dCalendar[Date]=[NextWkDy]))`

My problem is that instead of the filter function returning a single row for that given day like is should based on the "=" operator it is returning  a whole table starting from that date all the way until the end day and then becasue I am calculating a sum, it is giving me a really large number.  Why isn't it returning only the dates that match?

Note:

1.  I was able to make it work okay if I use calculated columns so I must be missing something

2.  I am using a sum istead of of a min, because at sum point I would like to be able to alter the code to return the sum of the productivity between 2 dates.

1 ACCEPTED SOLUTION

Accepted Solutions
v-cherch-msft Super Contributor

## Re: Help with dax filter function using measures as arguments.

You may refer to below measure:

```NextWkdyProd =
CALCULATE (
SUM ( dCalendar[Productivity] ),
FILTER (
dCalendar,
dCalendar[Date]
= MINX (
FILTER ( dCalendar, dCalendar[Date] > [End date] && dCalendar[Workday] = 1 ),
dCalendar[Date]
)
)
)```

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
v-cherch-msft Super Contributor

## Re: Help with dax filter function using measures as arguments.

You may refer to below measure:

```NextWkdyProd =
CALCULATE (
SUM ( dCalendar[Productivity] ),
FILTER (
dCalendar,
dCalendar[Date]
= MINX (
FILTER ( dCalendar, dCalendar[Date] > [End date] && dCalendar[Workday] = 1 ),
dCalendar[Date]
)
)
)```

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JoeRobert06 Regular Visitor

## Re: Help with dax filter function using measures as arguments.

I am having the same issue here, trying to make the filter context for a DAX table to be dynamce with either a slicer selection or a measure; see my below post as a reference. It looks like the method below is to wrap the argument with another filter() and then iterate across that table inside the original filter argument. I will try to see if solves my problem.

palvarez83, did this solve your problem?

Edit: I fogot to link my post, see below

https://community.powerbi.com/t5/Desktop/Dynamic-filter-argument-within-table-expression/m-p/630026

palvarez83 Regular Visitor

## Re: Help with dax filter function using measures as arguments.

@JoeRobert06 wrote:

I am having the same issue here, trying to make the filter context for a DAX table to be dynamce with either a slicer selection or a measure; see my below post as a reference. It looks like the method below is to wrap the argument with another filter() and then iterate across that table inside the original filter argument. I will try to see if solves my problem.

palvarez83, did this solve your problem?

Hi Joe,

I did eventually solve a similar issue.  Apparently I was running into the "implied calculate" issue with my measure.  How I solved it was when the measure was defined, in the calculate with fillter, I enclosed the tabled name with ALL().  This thread show how I did something similar.

https://community.powerbi.com/t5/Desktop/Lookup-DateKey-from-another-column-s-running-total/m-p/5888...

Announcements Learn the answers to some of the questions asked during the Amanda Triple A event. #### October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.  #### Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI. Top Kudoed Authors
Users Online
Currently online: 198 members 2,290 guests
Recent signins:
• philippfrenzel • dac • tienthb • Marac0105 • DougM • nagaraj007 • CarolineSH • thanatji • gunjan8421 • russtak • ciaranfkeane • bei • ethanlee • EmmaLReynolds 