Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
julesdude
Post Partisan
Post Partisan

Trouble with SUM - Multiple Filters and ORs Applied

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  
AAA12542Holding Over29-Sep-9028-Sep-15 
AAA6809Active23-Feb-2231-Dec-99 
AAA6809Active22-Jul-1621-Jul-26 
AAA6992Active29-Sep-1428-Sep-24 
AAA Active15-Sep-2214-Sep-37 
AAA12010Active19-Mar-1918-Sep-19 
AAA16575Active03-Feb-1202-Feb-27 
AAA7000Active14-May-2213-May-27 
AAA7000Active15-Aug-1310-Nov-99 
AAA12249Active09-Apr-1308-Apr-23 
AAA1241Active14-Aug-1913-Aug-29 
AAA Active01-Apr-1931-Dec-99 
AAA0Holding Over29-Apr-1528-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:

julesdude_0-1675898338003.png

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.

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

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

 

 

smpa01_0-1675909309385.png

 

Internally DAX generates this forEach Asset Reference dropped into the viz

smpa01_1-1675909366301.png

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

3 REPLIES 3
wdx223_Daniel
Super User
Super User

The comma in the fourth line from the bottom is redundant

Thanks @wdx223_Daniel  - that was it!

smpa01
Super User
Super User

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

 

 

smpa01_0-1675909309385.png

 

Internally DAX generates this forEach Asset Reference dropped into the viz

smpa01_1-1675909366301.png

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.