Slicer only show availeble values

[Anonymous]

Hi, 

I have in this example 3 tables, DimMainAccount, Calendar and FctAmount.
The relationship is 1 to many with cross direction single from DimMainAccount and Calendar to FctAmount.

I have a matrix visual that shows a value (SelectedMeasure, that is a switchmeasure based on AccountingCurrencyAmount in FctAmount) per MainAccount Category.
But as you can see the slicer MainAccountCategory show all the values that are in the DimMainAccount table and not only the ones with a value.

I doesn't work to take the SelectedMeasure into the filter pane for the MainAccountCategory and take SelectedMeasure <> 0. 
I guess I need some kind of measure like:

Selected Measure with Filter =

VAR SelectedYear = SELECTEDVALUE(Calendar[Year])

VAR SelectedMonth = SELECTEDVALUE('Calendar'[MonthNo])

RETURN

CALCULATE(

  SUM(FctAmount[AccountingCurrencyAmount]),

  FILTER(

    DimMainAccount,

    SUMX(

        FILTER(FctAmount, FctAmount[AccountingDate] >= DATE(SelectedYear, SelectedMonth, 1) && FctAmount[AccountingDate] <= EOMONTH(DATE(SelectedYear, SelectedMonth, 1), 0)),

        FctAmount[AccountingCurrencyAmount]

    ) <> 0

  ),

  CALENDAR(MIN(Calendar[Date]),MAX(Calendar[Date])),

  ALL(Calendar[Year]),

  ALL('Calendar'[MonthNo])

)

That measure doesnt work if I choose a year and multiple months, does anyone have an input of how you can solve this?

[ppm1]

Please seet this article for a simple approach with ISEMPTY to get that effect.

Filter slicers without using bidirectional filters in Power BI - SQLBI

Pat

[Anonymous]

Appricate your help, but unfortunatly it didnt worked out when choosing multiple Year + Months!