Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JB
Helper II
Helper II

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

JB_1-1650970154648.png

 

1 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_0-1651056334215.png

 

View solution in original post

17 REPLIES 17
tamerj1
Super User
Super User

Hi @JB 

if you delete CALCULATE along with its modifiers it should work

@JB 

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

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. 

@JB 

What is the relationship between the two tables?

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

 

JB_0-1650975332861.png

 

@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

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. 

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

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

 

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

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

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

JB_0-1650984788552.png

 

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

Still no success 😞

JB_0-1650987387017.png

 

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_0-1651056334215.png

 

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors