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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.