Slicer only show availeble values

Anonymous — Thu, 02 Feb 2023 12:29:16 GMT

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?

Re: Slicer only show availeble values

ppm1 — Thu, 02 Feb 2023 13:00:37 GMT

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

Re: Slicer only show availeble values

Anonymous — Thu, 02 Feb 2023 14:02:11 GMT

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

topic Re: Slicer only show availeble values in DAX Commands and Tips

Slicer only show availeble values

Re: Slicer only show availeble values

Re: Slicer only show availeble values