Help needed summing totals in column measure for my matrix table

[julesdude]

Hi,

I have a measure like this that provides me with a column in a matrix table:

Total Tenants (Grand Total) = 

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[Lease.Tenant Reference] ), FilterTable )

The measure applies a number of filters to a table called Lease_Unit, which contains all the data needed. The filters all work as they should, so that bit is all fine and doesn't need modifying. At the end, a distinct count of tenants is made, providing a total number of tenants.

Here is my table with the measure column at the very end:

My problems occur when I try to total all the values displayed here in the Tenants column into a card visual. I get an incorrect total that doesn't match all the values in this column summed.  I think what is happening is that when the measure is placed in this matrix table visual, unique tenants are calculated for each Building Name (the first column of the table which is also the field attribute in 'Rows' of the visual). Once that building name row level is lost, if drag the measure into a card visual the distinct count is made to the enture Lease_Unit table, and not reset per Building Name, which is what I want it to do.
Basically I want the distinct count to continue to apply for each Building Name, but not for ALL Building Names, for the correct total for my card visual.

Hope this makes sense. Help appreciated as always.

[PabloDeheza]

Hey @julesdude !
so, you are right on what the measure is doing. What you can do is force the count to be the sum of each Building Name by using SUMX. Try:

SUMX(
    VALUES( BuildingName ),
    [Total Tenants (Grand Total)]
)

Let me know if that helps!

[julesdude]

Hey @PabloDeheza 

Thanks for getting back!

I tried that with the SUMX but unfortuantely it leaves me a total of one more tenant than totalled in the table, so I couldn't use it. 

In the VALUES ( ) I use exactly the same field reference as I've got in my matrix table for the Building Name. 

I am out of ideas on what to do. 

[PabloDeheza]

Mmm, can you try instead of returning 

RETURN
CALCULATE ( DISTINCTCOUNT ( Lease_Unit[Lease.Tenant Reference] ), FilterTable )

Returning 

RETURN
SUMX( FilterTable, DISTINCTCOUNT ( Lease_Unit[Lease.Tenant Reference] ))

[julesdude]

@PabloDeheza 

That then gives me 111K tenants in the card visual. The total should be 220.

With the first SUMX example I was one out, so close, but not a match!

[PabloDeheza]

I'm gonna guess here and say we are dealing with referential integrity.

Can you make sure that your Buildings Dimension contains all building values that exists in you Fact table? The reason I tell you this is because there is a thing called Referential Integrity, when you have more distinct values on your fact table than you have on your Dimension then there is a referential integrity violation. The way Power BI handles this is by adding an invisible blank row in your dimension and that count as 1 in a DISTINCTCOUNT or other functions like that one.

Check this example:

https://www.linkedin.com/feed/update/urn:li:activity:7056539853378703360/

And this post to help you address the issue:

https://selfservicebi.co.uk/referential-integrity-violations-in-a-data-model-what-are-those/

Hope this helps.

[julesdude]

@PabloDeheza 

I think you're definitely onto the problem area there. I fear unpicking it because there are a lot of relationships built. 

Essentailly the core tables are the Lease Unit and the Building table. You are right in that there is a chance the Building table may contain items that are not in the Lease Unit table, but not vice versa.

I can't see why this would be a big problem though unless I had a number of references to both tables in the one visual. Perhaps I should ensure wherever possible I keep the matrix table referencing the Lease Unit table, as when it comes to summing the total of an established column total, it is breaking.

[julesdude]

Hi @PabloDeheza 

I created a new post with attached pbix to this:

Help needed! Can't get Card Visuals to Display Cor... - Microsoft Power BI Community