Adjusting a SUMX expression slightly so it counts unique results rather than sums

[julesdude]

Hi,

This is hopefully relatively straightforward. I've the following DAX expression that sums values from a table in my data model to provide results based on a set of filters/conditions:

Total Tenants 2 = 
VAR _asOfDate = [As Of Date]
VAR _assetref = MAX(Building[Asset Reference])

RETURN
VAR result = CALCULATE (
        SUMX (
            VALUES ( Lease_Uni[Unit Reference] ),
            SUMX (
                TOPN (
                    1,
                    FILTER (
                        Lease_Unit,
                        Lease_Unit[Unit Reference] = EARLIER ( Lease_Unit[Unit Reference] )
                    ),
                    Lease_Unit[Lease.Commencement Date]
                ),
                Lease_Unit[Leased Area]
            )
        ),
            
               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])
        )
    )
RETURN
result

1. How can I adapt the above so that it applies exactly the same filters that it is currently using to sum the results, but instead it counts the number of distinct results from a column in the table called [tenant reference]?

2. Is it correct that if I then use sumx, ([table], [measure] ) I can get the grand total so I can place this in a card visualisation?

I had begun with the approach below, but it is incorrect as it does not have all the filter logic from the above example. I'm not sure it is the right approach.

Total Tenants = 
VAR _table =
    FILTER (
        SUMMARIZECOLUMNS (
            Lease_Unit[Asset Reference],
            Lease_Unit[Lease.Tenant Reference]
        ),
        SUM(Lease_Unit[Leased Area]) > 0
    )
RETURN
    COUNTROWS ( _table )

Thank you

[johnt75 · Accepted solution]

Try

Num tenant references =
VAR _asOfDate = [As Of Date]
VAR _assetref =
    MAX ( Building[Asset Reference] )
VAR FilterTable =
    CALCULATETABLE (
        INDEX (
            1,
            Lease_Unit,
            ORDERBY ( Lease_Unit[Lease.Commencement Date], DESC ),
            PARTITIONBY ( Lease_Unit[Unit Reference] )
        ),
        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] )
        )
    )
RETURN
    CALCULATE ( DISTINCTCOUNT ( 'Lease unit'[tenant reference] ), FilterTable )

You should be able to just add this to a card visual and it will give you the total.

[julesdude]

HI @johnt75 

Although at row level when I used the measure as a column it gave me the correct results, it didn't sum the grand total when I applied the measure to the card visual unfortunately.

[johnt75]

Looks like you might need to do a SUMX over the values of whichever column is providing a unique ID for the row in your table or matrix visual

[julesdude]

Hi @johnt75 

Yes. I wrote the following:

Total Tenants (Grand Total) =
SUMX(Lease_Unit, [Total Tenants]) //the measure that was your solution above

However, this gives me more than the total by 14 additional tenants, so I am not sure my DAX is correct

[julesdude]

@johnt75 

Superb. Thank you so much. I will test in more detail but that seems to have done the job perfectly. 

I will definitely use this filter as a sort of 'template' measure whenever i need so I do not have to type out all the logic each time - I can assign it to that measure and reference it in any measure that needs it.