Trouble with SUM - Multiple Filters and ORs Applied

julesdude — Thu, 09 Feb 2023 00:13:01 GMT

Hi there,

Here is a modified extract from a table in my data model....bear in mind the table has a number of different Asset References, but I've listed a group of AAA here:

Asset Reference	Leased Area	Lease.Lease Status	Lease.Commencement Date	Lease.Expiration Date	Lease.Termination Date
AAA	12542	Holding Over	29-Sep-90	28-Sep-15
AAA	6809	Active	23-Feb-22	31-Dec-99
AAA	6809	Active	22-Jul-16	21-Jul-26
AAA	6992	Active	29-Sep-14	28-Sep-24
AAA		Active	15-Sep-22	14-Sep-37
AAA	12010	Active	19-Mar-19	18-Sep-19
AAA	16575	Active	03-Feb-12	02-Feb-27
AAA	7000	Active	14-May-22	13-May-27
AAA	7000	Active	15-Aug-13	10-Nov-99
AAA	12249	Active	09-Apr-13	08-Apr-23
AAA	1241	Active	14-Aug-19	13-Aug-29
AAA		Active	01-Apr-19	31-Dec-99
AAA	0	Holding Over	29-Apr-15	28-Apr-21

I am trying to SUM the Leased Area column. But I need to do so applying the following conditions:

1. The [Lease.Commencement Date] must be <= my measure [as of date] (as of date is a date picker in my report)

2. Either the [Lease.Expiration Date] must be >= [as of date] OR the [Lease.Lease Status] must either be 'Holding Over' OR 'Month-to-Month'

3. The [Lease.Termination Date] must be blank. However, if there is a date there, it must be >= [as of date]

Based on this logic with an [as of date] = 31/12/2022, I'd want the following green items summed for this asset:

My current DAX brings up the error - 'Argument 7 in CALCULATE function is required'. I do not know what is going wrong and not even sure my method is the best approach.
Current code:

Total Leased Area (Current) = VAR _asofdate = [As Of Date] VAR _assetref = MAX(Building[Asset Reference]) VAR _TotalLeasedArea = CALCULATE ( SUM(Lease_Unit[Leased Area] ), Lease_Unit[Asset Reference] = _assetref, Lease_Unit[Lease.Commencement Date] <= _asofdate, FILTER(Lease_Unit, Lease_Unit[Lease.Expiration Date] >= _asofdate && OR( Lease_Unit[Lease.Lease Status] = "Holding Over", Lease_Unit[Lease.Lease Status] = "Month-to-Month" ) ), FILTER(Lease_Unit, OR( Lease_Unit[Lease.Termination Date] >= _asofdate, ISBLANK( Lease_Unit[Lease.Termination Date] ) ) ), FILTER (Unit, OR ( Unit[Unit Start Date] <= _asofdate, ISBLANK(Unit[Unit Start Date] ) ) && OR ( Unit[Unit End Date] >= _asofdate, ISBLANK(Unit[Unit End Date]) ) ), ) RETURN _TotalLeasedArea

Any help appreciated with more efficient code without error.

Re: Trouble with SUM - Multiple Filters and ORs Applied

smpa01 — Thu, 09 Feb 2023 02:23:50 GMT

julesdude the table has to pass all 3 tests. So you can chain the test rather than find the instersection of all the conditions at once

Measure = VAR asOfDate = DATE ( 2022, 12, 31 ) VAR filtTblOne = FILTER ( tbl, tbl[Lease.Commencement Date] <= asOfDate ) VAR filtTblTwo = FILTER ( filtTblOne, tbl[Lease.Expiration Date] >= asOfDate || ( tbl[Lease.Lease Status] = "Holding Over" || tbl[Lease.Lease Status] = "Month-to-Month" ) ) VAR filtTblThree = FILTER ( filtTblTwo, tbl[Lease.Termination Date] = BLANK () || tbl[Lease.Termination Date] >= asOfDate ) RETURN CALCULATE ( SUM ( tbl[Leased Area] ), filtTblThree )

Internally DAX generates this forEach Asset Reference dropped into the viz

Re: Trouble with SUM - Multiple Filters and ORs Applied

wdx223_Daniel — Thu, 09 Feb 2023 03:03:06 GMT

The comma in the fourth line from the bottom is redundant

Re: Trouble with SUM - Multiple Filters and ORs Applied

julesdude — Thu, 09 Feb 2023 09:34:05 GMT

Thanks wdx223_Daniel - that was it!

topic Trouble with SUM - Multiple Filters and ORs Applied in DAX Commands and Tips

Trouble with SUM - Multiple Filters and ORs Applied

Re: Trouble with SUM - Multiple Filters and ORs Applied

Re: Trouble with SUM - Multiple Filters and ORs Applied

Re: Trouble with SUM - Multiple Filters and ORs Applied