Measure not calculating all rows on a table

[JB]

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. 

[tamerj1]

Hi @JB 

if you delete CALCULATE along with its modifiers it should work

[tamerj1]

@JB 

Can you please share the code for both Saturday Prior and Week Ending measures?

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

[tamerj1]

@JB 

What is the relationship between the two tables?

[JB]

Sheet1[Start_Date] is linked to Slicer Calendar[Date] (active, filters both ways)

[tamerj1]

@JB 
Try to add Start Date and End Date as measures (instead of coulumns ) using SELECTEDVALUE then use the new measures in your code instead of MAX

[JB]

The Start and End dates are the time frames for the projects, they aren't calculated and don't follow a pattern, they're human input. 

[tamerj1]

I know. But still you can add them as Measures using SELECTEDVALUE

[JB]

Sorry, I thought SELECTEDVALUE only worked on fields that were incorporated into a Slicer. 

I tried this though and it returned all 0s unfortunately. 

[tamerj1]

The problem with the bi-directional relationship. Now try to CROSSFILTER The relationship to NONE

CLACULATE(your expression, CROSSFILTER ( coulmn name table1, coumn name table2, NONE))

[JB]

Like this? It still isn't working, sorry. 

[tamerj1]

Don't use variables. Ise the measures directly inside calculate and remove ALL

[JB]

Still no success 😞

[JB · Accepted solution]

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

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

[amitchandak]

@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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[JB]

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.