Forum Discussion
Post PartisanPage Level Filter to Filter Out Certain Rows of Table Not Working
Hi,
I'm a little stuck trying to find a way to apply this.
I have a table called Lease-Unit in my data model that looks a bit like this:
| Asset | Asset Lease Unit Reference | Annual Net Rent | Lease Commencement Date |
| 1 | aaa1 | 100 | 15/02/2022 |
| 1 | aaa2 | 500 | 12/01/2022 |
| 1 | aaa2 | 300 | 12/03/2022 |
| 1 | aaa2 | 400 | 01/04/2023 |
| 1 | aaa3 | 400 | 01/01/2022 |
| 1 | aaa3 | 700 | 01/02/2022 |
| 1 | aaa4 | 200 | 01/05/2023 |
In my report, I have a table where I want to show the sum of Annual Net Rent for each Asset. The example above contains a breakdown of each lease for that asset - i have just shown asset 1 as an example.
The report contains a date picker where the user can select an as of date - the measure [as of date] retrieves this date.
I need to be able to filter out leases that come after the [as of date] selected by the user but also if the Asset Lease Unit Reference is repeated, to only keep the row where the Lease Commencement Date is closest to the [as of date] in the past. There are instances where sometimes the Asset Lease Unit Reference is duplicated, and I only want to keep the most recent Lease Commencement Date item for this.
In the case of the table above, with the filter I need applied at page level, if I selected an As Of Date of 13/04/2022 I would expect a table to look something like this:
| Asset | Asset Lease Unit Reference | Annual Net Rent |
| 1 | aaa1 | 100 |
| aaa2 | 300 | |
| aaa3 | 700 |
How do I achieve this?
12 Replies
johnt75Super User
Try
Annual Net Rent Measure = VAR CutOffDate = [as of date] RETURN CALCULATE ( SUM ( 'Table'[Annual Net Rent] ), INDEX ( 1, FILTER ( 'Table', 'Table'[Lease commencement date] <= CutOffDate ), ORDERBY ( 'Table'[Lease commencement date], DESC ), PARTITIONBY ( 'Table'[Asset], 'Table'[Asset Lease Unit Reference] ), MATCHBY ( 'Table'[Asset], 'Table'[Asset Lease Unit Reference], 'Table'[Lease commencement date] ) ) )
julesdudeThanks johnt75
Couple of things. How can this be adapted to be a page level filter applied to all visuals in my report? Can it be written to, say, produce a '0' or '1' and I filter by those validated rows it returns? Dragging the measure to my table doesn't seem to do filter out the rows I need.
Also, I get a red wiggly line under the DAX line for the PARTITIONBY function. This doesn't seem to stop the measure from running as I don't get an error in yellow at the bottom of the DAX box. Hovering my mouse over the red line it says 'Parameter is not the correct type'.
Thanks again for looking into it.- johnt75
The red wiggly line is because we've omitted an optional parameter, you can ignore it.
You can't use a measure as a page level or report level filter, so you would need to add it to each visual on the page.
If you wanted a measure to filter like that, you would need to make sure that the table has a unique identifier for each row. If it doesn't have one already you could add an index column using Power Query. In Modelling view select the table and choose the unique column as the key column, then you can create a measure like
My filter = VAR CurrentID = SELECTEDVALUE ( 'Table'[Row Number] ) VAR SummaryTable = INDEX ( 1, FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Lease commencement date] <= CutOffDate ), ORDERBY ( 'Table'[Lease commencement date], DESC ), PARTITIONBY ( 'Table'[Asset], 'Table'[Asset Lease Unit Reference] ) ) VAR Result = INT ( CurrentID IN SELECTCOLUMNS ( SummaryTable, 'Table'[Row Number] ) ) RETURN Resultand use that as a visual level filter to only show where the value is 1.