Forum Discussion

JB's avatar
JB
Icon for Helper II rankHelper II
4 years ago
Solved

Measure 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 ...
all
CALCULATE
if
Max
measure
Table
  • JB's avatar
    JB
    4 years ago

    I got it to work correctly by changing the relationship between the two tables to a One to Many (the slicer date calendar being the one).

     

    Now that I've copied the technique to the actual project I'm working on, it yields mixed results - as you can see from the below screenshot, some rows that should have 1s have 0s, even when they have the identical start/end dates as rows with 1s. Do you have any idea what's causing this?

    Thanks

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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])-7

return

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

  • JB's avatar
    JB
    Icon for Helper II rankHelper II
    4 years ago

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