topic Re: Adjusting a SUMX expression slightly so it counts unique results rather than sums in DAX Commands and Tips

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

julesdude — Tue, 18 Apr 2023 10:02:23 GMT

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

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

johnt75 — Tue, 18 Apr 2023 10:56:21 GMT

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.

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

julesdude — Tue, 18 Apr 2023 11:46:42 GMT

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.

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

julesdude — Tue, 18 Apr 2023 16:02:04 GMT

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.

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

johnt75 — Tue, 18 Apr 2023 16:07:20 GMT

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

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

julesdude — Tue, 18 Apr 2023 16:14:26 GMT

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