Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
75 | |
51 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |