topic Re: Measure not calculating all rows on a table in DAX Commands and Tips

Measure not calculating all rows on a table

JB — Tue, 26 Apr 2022 10:56:24 GMT

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.

Re: Measure not calculating all rows on a table

amitchandak — Tue, 26 Apr 2022 11:14:17 GMT

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

Re: Measure not calculating all rows on a table

tamerj1 — Tue, 26 Apr 2022 11:17:56 GMT

Hi JB

if you delete CALCULATE along with its modifiers it should work

Re: Measure not calculating all rows on a table

JB — Tue, 26 Apr 2022 11:36:06 GMT

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.

Re: Measure not calculating all rows on a table

JB — Tue, 26 Apr 2022 11:38:41 GMT

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.

Re: Measure not calculating all rows on a table

tamerj1 — Tue, 26 Apr 2022 11:46:13 GMT

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

Re: Measure not calculating all rows on a table

JB — Tue, 26 Apr 2022 11:54:39 GMT

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.

Re: Measure not calculating all rows on a table

tamerj1 — Tue, 26 Apr 2022 12:08:23 GMT

What is the relationship between the two tables?

Re: Measure not calculating all rows on a table

JB — Tue, 26 Apr 2022 12:20:19 GMT

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

Re: Measure not calculating all rows on a table

tamerj1 — Tue, 26 Apr 2022 12:45:31 GMT

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

Re: Measure not calculating all rows on a table

JB — Tue, 26 Apr 2022 13:17:21 GMT

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.

Re: Measure not calculating all rows on a table

tamerj1 — Tue, 26 Apr 2022 14:12:48 GMT

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

Re: Measure not calculating all rows on a table

JB — Tue, 26 Apr 2022 14:39:19 GMT

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

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

Re: Measure not calculating all rows on a table

tamerj1 — Tue, 26 Apr 2022 14:42:27 GMT

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

Re: Measure not calculating all rows on a table

JB — Tue, 26 Apr 2022 14:54:22 GMT

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

Re: Measure not calculating all rows on a table

tamerj1 — Tue, 26 Apr 2022 15:16:05 GMT

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

Re: Measure not calculating all rows on a table

JB — Tue, 26 Apr 2022 15:37:36 GMT

Still no success 😞

Re: Measure not calculating all rows on a table

JB — Wed, 27 Apr 2022 10:49:24 GMT

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