cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
palvarez83 Regular Visitor
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
Super Contributor

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

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

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.

View solution in original post

3 REPLIES 3
v-cherch-msft Super Contributor
Super Contributor

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

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

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.

View solution in original post

JoeRobert06 Regular Visitor
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
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...

 

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

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.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 198 members 2,290 guests
Please welcome our newest community members: