Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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.
Solved! Go to 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
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.
Sheet1[Start_Date] is linked to Slicer Calendar[Date] (active, filters both ways)
@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.
Don't use variables. Ise the measures directly inside calculate and remove ALL
Still no success 😞
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
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.
@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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
20 | |
19 | |
13 | |
12 |
User | Count |
---|---|
41 | |
29 | |
23 | |
22 | |
22 |