Forum Discussion
Help with filtering table and using that table as context
I realize that my subject is a bit vague so let me start with a practical example. I have the following data:
| 1 | 10/1/2025 |
| 2 | 10/10/2025 |
| 3 | 10/11/2025 |
| 4 | 10/12/2025 |
and I want to generate a new column which provides for me a running total of dates that appear after 10/9/2025. So the resulting column would look like:
| 1 | 10/1/2025 | 0 |
| 2 | 10/10/2025 | 1 |
| 3 | 10/11/2025 | 2 |
| 4 | 10/12/2025 | 3 |
The idea is that we essentially get a "burndown" of rows that have dates appearing after a specific point in time and I can generate a nice visual based on that. I have created a simple DAX query to generate the table above:
New Score Applied Date =
var cur = SELECTEDVALUE('table'[Changed Date])
var calc = CALCULATE(COUNTROWS(ALL('table')), CALCULATE(COUNTROWS('table'), FILTER(ALL('table'), 'table'[Changed Date] <= cur && 'table'[Changed Date] >= DATE(2025, 10, 9)))
return calcWhich works great when the table is a direct query. However, I've changed the table to add a new column:
| 1 | 10/1/2025 | True |
| 2 | 10/10/2025 | False |
| 2 | 10/10/2025 | True |
| 3 | 10/11/2025 | True |
| 4 | 10/12/2025 | True |
This new column records whether or not the entry is the most up-to-date version of the data. This means I have to first filter out the values which are 'False' and then I can start counting dates like the above.
Despite my best efforts, I have not been able to create a reasonable query to make this happen. I've tirelessly searched for queries where I can make a slicer work with a calculated table like the following:
New Table =
var sel = SELECTEDVALUE('table'[Date], DATE(2025, 1, 1))
var curr = CALCULATETABLE('table', FILTER('table', 'table'[Is Current]))
var calc = COUNTROWS(curr) - CALCULATE(COUNTROWS(curr), FILTER(curr, 'table'[Changed Date] <= sel && 'table'[Changed Date] >= DATE(2025, 10, 9)))
return ADDCOLUMNS(curr, "new", calc)But this does not work, as I've found, because slicers are fundamentally incompatible with calculated tables/measures. It's not exactly clear to me how I can both filter out the 'False' values while also counting rows on that resulted set to compute the running date totals.
Create a calculated column:
Burndown Count =
VAR CurrentDate = 'table'[Changed Date]
VAR FilteredTable = FILTER(ALL('table'), 'table'[Is Current] = TRUE())
RETURN
COUNTROWS(
FILTER(
FilteredTable,
'table'[Changed Date] >= DATE(2025, 10, 9) &&
'table'[Changed Date] <= CurrentDate
)
)
6 Replies
- Kedar_PandeSuper User
Create a calculated column:
Burndown Count =
VAR CurrentDate = 'table'[Changed Date]
VAR FilteredTable = FILTER(ALL('table'), 'table'[Is Current] = TRUE())
RETURN
COUNTROWS(
FILTER(
FilteredTable,
'table'[Changed Date] >= DATE(2025, 10, 9) &&
'table'[Changed Date] <= CurrentDate
)
)- CADACAMAMicrosoft Employee
This is exactly what I was looking for! I believe the thing I was missing was how to convey the row value of a filtered table as context for the inner-filter. Making a column filter based on the computed table was the missing piece. Thank you!
- lbendlinSuper User
You need to pack the entire logic into a new measure.
SELECTEDVALUE() means nothing when creating a calculated table. It can only ever be used with measures.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523- CADACAMAMicrosoft Employee
This is a real sample of the data:
1 10/1/2025
True
2 10/10/2025
False
2 10/10/2025 True 3 10/11/2025 True 4 10/12/2025 True
After we filter out the 'False' entries I expect the following table:1 10/1/2025
True
2 10/10/2025 True 3 10/11/2025 True 4 10/12/2025 True Then after applying the date accumulation (based on dates after 10/9/2025) I expect the following table (with a newly added column):
1 10/1/2025
True
0
2 10/10/2025 True 1 3 10/11/2025 True 2 4 10/12/2025 True 3
Keep in mind, that last generated column is based on the filtered table. The value calculated there is equivalent to saying something like: "count all dates prior to this date but after 10/9/2025".- Ashish_MathurSuper User
- Ashish_MathurSuper User
Hi,
I think we should solve this with a measure. Based on the third table that you have selected, show the expected result.