Forum Discussion

CADACAMA's avatar
CADACAMA
Microsoft Employee
9 months ago
Solved

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

210/10/2025
310/11/2025
410/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

210/10/20251
310/11/20252
410/12/20253

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 calc

Which 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

210/10/2025True
310/11/2025True
410/12/2025True

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

    CADACAMA

6 Replies

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

    CADACAMA

    • CADACAMA's avatar
      CADACAMA
      Microsoft 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!

  • 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

    • CADACAMA's avatar
      CADACAMA
      Microsoft Employee

      This is a real sample of the data:

      1

      10/1/2025

      True

      2

      10/10/2025

      False

      210/10/2025True
      310/11/2025True
      410/12/2025True


      After we filter out the 'False' entries I expect the following table:

      1

      10/1/2025

      True

      210/10/2025True
      310/11/2025True
      410/12/2025True

       

      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

      210/10/2025True1
      310/11/2025True2
      410/12/2025True3


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

  • Hi,

    I think we should solve this with a measure.  Based on the third table that you have selected, show the expected result.