Forum Discussion

Derekp978's avatar
Derekp978
Frequent Visitor
4 years ago

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? 



1 Reply

  • v-zhangti's avatar
    v-zhangti
    Community Support

    Hi, Derekp978 

     

    Can you provide simple pbix files for testing? Initially, I think it's a filter issue.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.