Non-additive measure for a matrix with a slicer.

[Johnners]

Hi,

I created the following measure to lookup data in a table. It works perfectly by itself but the matrix disappears when I add slicers and select a level of the matrix which is not visible in the matrix. I want it to behave as if it was using additive data. I have attached a page (additive) with the problem choosing Argentina in the  L4 slicer. The matrix has disappeared. The other page gives the same with an additive measure and showing the behavior I would like. I would be very grateful with help to correct this measure to give the behavior I need. I attached a link to the file. Thanks.

Len_CM =

VAR SelectedMonth = FORMAT([Len_LatestDate],"mmm")

VAR SelectedYear = FORMAT([len_LatestDate],"yyyy")

VAR L6 = CALCULATE([Len_Amt],Countries[LevTot]="6",Calendar[Month] = SelectedMonth, Calendar[Year] = SelectedYear)

VAR L5 = CALCULATE([Len_Amt],Countries[LevTot]="5",Calendar[Month]= SelectedMonth, Calendar[Year] = SelectedYear)

VAR L4 = CALCULATE([Len_Amt],Countries[LevTot]="4",Calendar[Month] = SelectedMonth, Calendar[Year] = SelectedYear)

VAR L3 = CALCULATE([Len_Amt],Countries[LevTot]="3",Calendar[Month] = SelectedMonth, Calendar[Year] = SelectedYear)

VAR L2 = CALCULATE([Len_Amt],Countries[LevTot]="2",Calendar[Month] = SelectedMonth, Calendar[Year] = SelectedYear)

VAR L1 = CALCULATE([Len_Amt],Countries[LevTot]="1",Calendar[Month] = SelectedMonth, Calendar[Year] = SelectedYear)

VAR L6selected = ISINSCOPE ( Countries[L6])

VAR L5selected = ISINSCOPE ( Countries[L5])

VAR L4selected = ISINSCOPE ( Countries[L4])

VAR L3selected = ISINSCOPE ( Countries[L3])

VAR L2selected = ISINSCOPE ( Countries[L2])

VAR L1selected = ISINSCOPE ( Countries[L1])

VAR Result = SWITCH(TRUE(),

L6selected, L6,

L5selected, L5,

L4selected, L4,

L3selected, L3,

L2selected, L2,

L1)

RETURN

Result

What I want above.

 Non-additive measure problem 

[johnt75]

If the measure is genuinely non-additive then you could change the measure to return Result + 0. That would force a value to be returned for each level of the hierarchy, allowing you to drill down until you reach the level which does return a value.

If the measure is additive, which this appears to be, you could simplify it to 

Len_CM =
VAR SelectedMonth = FORMAT( [Len_LatestDate], "mmm" )
VAR SelectedYear = FORMAT( [Len_LatestDate], "yyyy" )
VAR Result =
	CALCULATE(
		[Len_Amt],
		'Calendar'[Month] = SelectedMonth,
		'Calendar'[Year] = SelectedYear
	)
RETURN
	Result