Help with dax filter function using measures as arguments.

[palvarez83]

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.

[v-cherch-msft · Accepted solution]

Hi @palvarez83

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

[JoeRobert06]

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]

---

@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...