Forum Discussion

palvarez83's avatar
palvarez83
Helper I
7 years ago
Solved

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.

 

 

  • 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

3 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee

    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's avatar
      JoeRobert06
      Helper II

      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's avatar
        palvarez83
        Helper I

        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/588868#M279273