Forum Discussion
Tabular Model Showing Different Values Locally vs Server with AllExcept
All I have a larger tabular model with 3 key tables in play for my issue -
Client - Contain Line of Business
Added Date - Table containing values for when a transaction is added
properties - Fact table containing all property transactions; Contains a Field for a Score Bin (calculated on the back end)
Need - I need the count of properties to span accross the entire Line of Business(LOB) which pivoted. The main case is because the new Visual with Small Multiples can only calculate % over a Grand Total and not a Sub Total.
Measures:
Properties in Bin 20 Denominator:=
VAR mindate = MIN('Added Date'[Added Date])
VAR maxdate = MAX('Added Date'[Added Date])
RETURN CALCULATE(
[Count of Properties]
, 'Properties'[Visual Score (Bin 20)] <> BLANK()
,ALLEXCEPT(Clients,Clients[Client LOB])
, FILTER('Added Date', 'Added Date'[Added Date] >= mindate && 'Added Date'[Added Date] <= maxdate)
)% Properties in Bin 20:=
VAR mindate = MIN('Added Date'[Added Date])
VAR maxdate = MAX('Added Date'[Added Date])
RETURN DIVIDE(
[Count of Properties]
, CALCULATE(
[Count of Properties]
, 'Properties'[Visual Score (Bin 20)] <> BLANK()
,ALLEXCEPT(Clients,Clients[Client LOB])
, FILTER('Added Date', 'Added Date'[Added Date] >= mindate && 'Added Date'[Added Date] <= maxdate)
)
, BLANK()
)
When a use "Analyze in Excel" from Visual Studio the Measures and Output react as expected -
When I deploy the Tabular Model and look at it in PBI Desktop or on the Server I get - It IS respecting the Non-Blank clause - but NOT the ALLEXCEPT
Any ideas as to why the Excel / Server models are different - OR - is there anything wrong with the DAX?