Forum Discussion
Helper IIMeasure not calculating all rows on a table
Hello,
I need to make a table that only shows projects that started during or before the selected week, and are due to finish during or after the selected week.
I intend to use a binary 1/0 result to filter eligible results and have been testing this measure with a simple dataset in which all entries should return a 1 - but they aren't and I can't work out why. I've added ALL to the filter part of the CALCULATE expression, so it shouldn't be excluding any data.
Any suggestions would be appreciated.
17 Replies
- amitchandak
Super User
JB , prefer to have and independent date table with week
//assume week is selected
Measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])var _max1 = maxx(allselected(Date),Date[Date]) -7
var _min1 = minx(allselected(Date),Date[Date])-7return
CALCULATE(count('Table'[ID]), FILTER(Table,'Table'[Start Date] >= _min1 && 'Table'[Start Date] <=_max1 && 'Table'[End Date] >= _min && 'Table'[EndDate] <=_max ))
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8
JBI have an independent date table that is used to slice the Week Ending date - the columns of dates shown above is just the simplified version to get the basics working.
I tried your CAL(COUNT(FILTER method but unfortunately it returns nothing, just empty cells.
- JB
It does, but once I complicate things by adding a date slicer to determine the Week Ending date, it continues to return 1 even for rows that should be 0.
- JB
It's supposed to work with a slicer that's connected to a date table consisting exclusively of Fridays.
var WE = SELECTEDVALUE('Slicer Calendar'[Week Ending]) var SP = (SELECTEDVALUE('Slicer Calendar'[Week Ending])-6)I disabled the slicer from interacting with the table and, as you can see above, it returns the Friday and Saturday dates for the week specified in the Start Date column.
I assume that these are not the source of the problem since they are correctly calling out valid dates, so the calculation has a legitimate date range to work with.
