Counting Rows of the Results given by a Measure from a Column in a Table

[julesdude]

Hi all,

I have the following table in my report:

I am trying to work out a DAX logic that will effectively distinct count the number of tenants for each Building Name.

Already in Leased Area column is a measure which successfully applied a number of filters for rows of the table in the data model and sums up the values accordingly to give the total against that corresponding Building Name.

I want to use the same filter logic but perform a count of the results after that filtering is applied.

I have 2 questions:

1. What's the best way to use count to do this?

2. Since the same filtering logic might be used for additional column measures I might add to this table, is it more sensible to apply the filter one time as a measure in the filter pane and write these addiditional column measure calculations assuming this base filter logic has already been applied? For example, I could apply the base filtering in the filter pane for the visual with a conditional IF statement to say if all conditions are met for a row in the table then flag as 1, then filter just the 1s etc.

My measue for the Leased Area column of the table above is:

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

RETURN
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[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
IF( result = BLANK() && ISBLANK([Total Leasable Area (Current)]) = FALSE, 0, result)

The measue I'd been using for the Tenants column, which is currently incorrect, is:

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

How best should I go about using the filtering inherent in [Total Leased Area] to make a count of the results for each buillding name per row of the table?
How can I then create a card visual with the grand total of all tenants?

[Anonymous]

Hi @julesdude,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng