Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
julesdude
Post Partisan
Post Partisan

Page Level Filter to Filter Out Certain Rows of Table Not Working

‎07-17-2023 12:16 PM

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
1aaa110015/02/2022
1aaa250012/01/2022
1aaa230012/03/2022
1aaa240001/04/2023
1aaa340001/01/2022
1aaa370001/02/2022
1aaa420001/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
1aaa1100
 aaa2300
 aaa3700

 

How do I achieve this?

Labels:
Message 1 of 13
808 Views
0
12 REPLIES 12
johnt75
Super User
Super User

‎07-18-2023 02:42 AM

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] )
        )
    )
Message 2 of 13
768 Views
julesdude
Post Partisan
Post Partisan
In response to johnt75

‎07-18-2023 04:52 AM

Thanks @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.

Message 3 of 13
766 Views
0
johnt75
Super User
Super User
In response to julesdude

‎07-18-2023 05:50 AM

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
    Result

and use that as a visual level filter to only show where the value is 1.

Message 4 of 13
763 Views
julesdude
Post Partisan
Post Partisan
In response to johnt75

‎07-18-2023 09:13 AM

Hi @johnt75 

Thanks again for this. Yes i did have an index column so tried adapting using that. I've been testing and playing a bit, but it is still not working.

It appears to me that this is a larger issue. When I apply the measure to the visual level filter with the condition to filter if is '1', I only get one lease coming through in my table which is for Lease Reference '2'. It's best if I give you the full dateset, with references changed for anonymity. 

Asset Reference  Lease Reference  Unit Reference  Annual Net Rent  Column19  Lease_Commencement Date  Asset Lease Unit Reference  Lease_Lease Status  Lease_Commencement Date  Lease_Expiration Date  Lease_Termination Date  Unit_Unit Start Date  Unit_Unit End Date  
aaa1P15100149301-Mar-18aaa_1_P15Active01-Mar-1831-Aug-27 28-Jun-19 
aaa1B10100149201-Mar-18aaa_1_B10Active01-Mar-1831-Aug-27 28-Jun-19 
aaa107A100149101-Mar-18aaa_1_07AActive01-Mar-1831-Aug-27 28-Jun-19 
aaa200A100149001-Feb-19aaa_2_00AActive01-Feb-1931-Jan-28 28-Jun-19 
aaa3B01100149601-Apr-22aaa_3_B01Active01-Apr-2231-Oct-30 28-Jun-19 
aaa302A100149401-Apr-22aaa_3_02AActive01-Apr-2231-Oct-30 28-Jun-19 
aaa301A100149501-Apr-22aaa_3_01AActive01-Apr-2231-Oct-30 28-Jun-19 
aaa3P14100150801-Apr-22aaa_3_P14Active01-Apr-2231-Oct-30 28-Jun-19 
aaa3P13100150701-Apr-22aaa_3_P13Active01-Apr-2231-Oct-30 28-Jun-19 
aaa3P12100150601-Apr-22aaa_3_P12Active01-Apr-2231-Oct-30 28-Jun-19 
aaa3P09100150501-Apr-22aaa_3_P09Active01-Apr-2231-Oct-30 28-Jun-19 
aaa3B09100150401-Apr-22aaa_3_B09Active01-Apr-2231-Oct-30 28-Jun-19 
aaa3B08100150301-Apr-22aaa_3_B08Active01-Apr-2231-Oct-30 28-Jun-19 
aaa3B07100150201-Apr-22aaa_3_B07Active01-Apr-2231-Oct-30 28-Jun-19 
aaa3B06100150101-Apr-22aaa_3_B06Active01-Apr-2231-Oct-30 28-Jun-19 
aaa3B05100150001-Apr-22aaa_3_B05Active01-Apr-2231-Oct-30 28-Jun-19 
aaa3B04100149901-Apr-22aaa_3_B04Active01-Apr-2231-Oct-30 28-Jun-19 
aaa3B03100149801-Apr-22aaa_3_B03Active01-Apr-2231-Oct-30 28-Jun-19 
aaa3B02100149701-Apr-22aaa_3_B02Active01-Apr-2231-Oct-30 28-Jun-19 
aaa4P12100488101-Nov-16aaa_4_P12Active01-Nov-1631-Oct-25 28-Jun-19 
aaa401A100487001-Nov-16aaa_4_01AActive01-Nov-1631-Oct-25 28-Jun-19 
aaa402A100486901-Nov-16aaa_4_02AActive01-Nov-1631-Oct-25 28-Jun-19 
aaa4P14100488301-Nov-16aaa_4_P14Active01-Nov-1631-Oct-25 28-Jun-19 
aaa4P13100488201-Nov-16aaa_4_P13Active01-Nov-1631-Oct-25 28-Jun-19 
aaa4P09100488001-Nov-16aaa_4_P09Active01-Nov-1631-Oct-25 28-Jun-19 
aaa4B09100487901-Nov-16aaa_4_B09Active01-Nov-1631-Oct-25 28-Jun-19 
aaa4B08100487801-Nov-16aaa_4_B08Active01-Nov-1631-Oct-25 28-Jun-19 
aaa4B07100487701-Nov-16aaa_4_B07Active01-Nov-1631-Oct-25 28-Jun-19 
aaa4B06100487601-Nov-16aaa_4_B06Active01-Nov-1631-Oct-25 28-Jun-19 
aaa4B05100487501-Nov-16aaa_4_B05Active01-Nov-1631-Oct-25 28-Jun-19 
aaa4B04100487401-Nov-16aaa_4_B04Active01-Nov-1631-Oct-25 28-Jun-19 
aaa4B03100487301-Nov-16aaa_4_B03Active01-Nov-1631-Oct-25 28-Jun-19 
aaa4B02100487201-Nov-16aaa_4_B02Active01-Nov-1631-Oct-25 28-Jun-19 
aaa4B01100487101-Nov-16aaa_4_B01Active01-Nov-1631-Oct-25 28-Jun-19 

 

I realise that there are some other factors at play here so I have included additional columns. I went back to some of the other measures I'm applying that filter correctly and take out relevant rows based on conditions, as I know they are summing columns in the table correctly.

Here's a measure I am using that is working. This works if I apply it to columns in a table but I cannot obviously use it dragging it to the Rows section of the table if, say, I have Asset Reference and then Lease Reference underneath it. Is it more effecient to just apply one measure with all the filtering in it to a visual level filter. Or do I adapt the measure for each column in my table. I'm sensing it's more efficient to just do the filtering once at visualisation level but if so how do I adapt the measure to get the '1' and '0' result I can use as the filter value to ignore the rows I need?
DAX:

 

 

Total Annual Net Rent (Current) = 
VAR _asOfDate = [As Of Date]
VAR _assetref = MAX(Building[Asset Reference])
VAR result = CALCULATE (
        SUMX (
            VALUES ( Lease_Unit[Unit Reference] ),
            SUMX (
                TOPN (
                    1,
                    FILTER (
                        Lease_Unit,
                        Lease_Unit[Unit Reference] = EARLIER ( Lease_Unit[Unit Reference] )
                    ),
                    Lease_Unit[Lease_Commencement Date]
                ),
                Lease_Unit[Annual Net Rent]
            )
        ),
            
               OR(
                    ISBLANK ( Lease_Unit[Unit_Unit Start Date] ),
                    Lease_Unit[Unit_Unit Start Date] <= _asofdate
                )
                && OR (
                    ISBLANK ( Lease_Unit[Unit_Unit End Date] ),
                    Lease_Unit[Unit_Unit End Date] >= _asofdate
                    )
           , 
        AND (
            Lease_Unit[Lease_Expiration Date] >= _asOfDate,
            Lease_Unit[Lease_Commencement Date] <= _asOfDate
        )
            || Lease_Unit[Lease_Lease Status] IN { "Holding Over", "Month-to-Month" },
        OR( 
            Lease_Unit[Lease_Termination Date] >= _asOfDate, 
        isblank(Lease_Unit[Lease_Termination Date])
        )
        
       // COALESCE ( Lease_Unit[Lease_Termination Date], TODAY() ) >= _asOfDate
    )
RETURN
result

 

 




Message 5 of 13
753 Views
0
johnt75
Super User
Super User
In response to julesdude

‎07-19-2023 02:27 AM

The below seems to work

 

Is Visible = 
VAR AsOfDate = [As of date]
VAR  VisibleIDs = SELECTCOLUMNS(
	INDEX(
		1,
		CALCULATETABLE(
			'Table 3',
			( ISBLANK( 'Table 3'[Unit_Unit Start Date] ) || 'Table 3'[Unit_Unit Start Date] <= AsOfDate ) &&
			( ISBLANK( 'Table 3'[Unit_Unit End Date] ) || 'Table 3'[Unit_Unit End Date] >= AsOfDate ),
			( 'Table 3'[Lease_Commencement Date] <= AsOfDate && 'Table 3'[Lease_Expiration Date] >= AsOfDate ) ||
			'Table 3'[Lease_Lease Status] IN { "Holding Over", "Month-to-Month" },
			'Table 3'[Lease_Termination Date] >= AsOfDate || ISBLANK( 'Table 3'[Lease_Termination Date] )
		),
		ORDERBY( 'Table 3'[Lease_Commencement Date], DESC ),
		PARTITIONBY( 'Table 3'[Asset Reference], 'Table 3'[Lease Reference] )
	),
	"@value", 'Table 3'[Column19]
)
VAR Result = INT( SELECTEDVALUE( 'Table 3'[Column19]) IN VisibleIDs)
RETURN Result

 

Message 6 of 13
734 Views
julesdude
Post Partisan
Post Partisan
In response to johnt75

‎07-19-2023 03:37 AM

Thanks @johnt75 again for your help.

I've applied the filter to show items when value is '1'.

The matrix table I have has, under the Rows, Asset Reference, and then under that, Lease Reference, so you can click to expand this parent/child relationship.

Latest solution does filter out leases but too many of them. For the asset 'aaa', with the filter measure applied only Lease Reference '2' shows. This is if my as of date is 30/06/2023. 
What I'd expect to see in the matrix table in my report, fully exapnded, is:

As of Date = 30/06/2023

Asset Reference  Lease Reference  Annual Net Rent   
aaa1300All 3 unit lines against this lease reference are valid
 21001 valid unit for this valid lease.
 31500A good few units here against this active lease
    

Lease reference 4 is not listed because although all the conditions are met that say the lease and the unit are active, we already have an active lease against all the units (lease ref 3) which has a Lease_Commencement Date closer to the [as of date] than the Lease_Commencement Date in Lease Reference 4. You can't have more than 1 active lease against an active unit so the more recent lease commencement date wins.
I'm not sure why the above is only making visible lease ref 2. Even creating a basic table with just the Asset Reference and Lease Reference as rows shows just lease ref 2.

Message 7 of 13
729 Views
0
johnt75
Super User
Super User
In response to julesdude

‎07-19-2023 04:04 AM

I've edited my post, changing the PARTITIONBY to use the Lease Reference column.

Message 8 of 13
726 Views
julesdude
Post Partisan
Post Partisan
In response to johnt75

‎07-19-2023 04:20 AM

Thanks again @johnt75 

Unfortunately still the same result - I only get Lease Reference '2' appearing against the asset.

Message 9 of 13
722 Views
0
johnt75
Super User
Super User
In response to julesdude

‎07-19-2023 06:03 AM

Can you use your existing measure for net rent to filter out values where it is blank?

Message 10 of 13
717 Views
0
julesdude
Post Partisan
Post Partisan
In response to johnt75

‎07-19-2023 06:21 AM

@johnt75 

Well my existing measure for that is:

Total Annual Net Rent (Current) = 
VAR _asOfDate = [As Of Date]
VAR _assetref = MAX(Building[Asset Reference])
VAR result = CALCULATE (
        SUMX (
            VALUES ( Lease_Unit[Unit Reference] ),
            SUMX (
                TOPN (
                    1,
                    FILTER (
                        Lease_Unit,
                        Lease_Unit[Unit Reference] = EARLIER ( Lease_Unit[Unit Reference] )
                    ),
                    Lease_Unit[Lease_Commencement Date]
                ),
                Lease_Unit[Annual Net Rent]
            )
        ),
            
               OR(
                    ISBLANK ( Lease_Unit[Unit_Unit Start Date] ),
                    Lease_Unit[Unit_Unit Start Date] <= _asofdate
                )
                && OR (
                    ISBLANK ( Lease_Unit[Unit_Unit End Date] ),
                    Lease_Unit[Unit_Unit End Date] >= _asofdate
                    )
           , 
        AND (
            Lease_Unit[Lease_Expiration Date] >= _asOfDate,
            Lease_Unit[Lease_Commencement Date] <= _asOfDate
        )
            || Lease_Unit[Lease_Lease Status] IN { "Holding Over", "Month-to-Month" },
        OR( 
            Lease_Unit[Lease_Termination Date] >= _asOfDate, 
        isblank(Lease_Unit[Lease_Termination Date])
        )
        
       // COALESCE ( Lease_Unit[Lease_Termination Date], TODAY() ) >= _asOfDate
    )
RETURN
result

 

This applies the filter as desired and only sums Annual Net Rent column from the rows that remain. But I don't know how I can adapt this to give me the '1' for each of those remaining rows that can then allow me to filter by those 1s.

Message 11 of 13
714 Views
0
johnt75
Super User
Super User
In response to julesdude

‎07-19-2023 06:45 AM

I don't think you need to adapt it, you can just apply a filter to that measure to only show when it is not blank.

Message 12 of 13
711 Views
julesdude
Post Partisan
Post Partisan
In response to johnt75

‎07-20-2023 11:29 AM

@johnt75 
Well I have just been trying a few methods of doing just that but none are working. The problem is when I compose my matrix table in the Rows section using fields Asset Reference then Lease Reference underneath it. If it's just Asset Reference there then the measure works ok. If I have Lease Reference as the next expanded row, it seems to force the measure to calculate at lease level and give the total for it, which means the calculation doesn't consider whether, when all the filtering conditions in the measure have been applied, that if there are more than one rows with the same Asset, Lease and Unit reference to only pick the one with the latest Lease Commencement Date. That part seems to be bypassed when I'm splitting at Lease level in the table. 
I looked at using COUNTROWS and SUMMARIZE at the beginning of my measure to look at ways of capturing this result in a filter to filter this out. Or using an (IF.......IS EMPTY ......0 , 1)...at the beginning of the measure to see if I could catch whether results occur or not. 
Still no joy unfortunately.

Message 13 of 13
693 Views
0

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

View All