DAX: Show only available Filter Options without Cross-Filter Direction

[Titatovenaar2]

Hi guys,

I want to be able to have a list of filter options that are available in another related table.

This is the model I use:

As you can see below there are in the Dimension 8 ID's total (1,2,3,4,5,6,7,8)

While in the Fact there is only (0,1,2,3,4,5).

I only want to see 1,2,3,4,5 instead of 1,2,3,4,5,6,7,8

With Crossfilter direction to 'both' I get the result I want, like below:

Unfortunately, the model I'm working on won't allow me to set it to crossfilter direction 'both', and also, it is widely discouraged online to set the crossfilter direction to 'both'.

Any suggestions how to deal with unavailable filter options using maybe Measures / CalculatedTable / CalculatedColumns to filter the filter itself?

Kind regards,

Igor

[Titatovenaar2]

Oops.. ;). Thanks!

[amitchandak · Accepted solution]

@Titatovenaar2 , if you want to reduce the value of the dimension slicer or table, you can add a visual level filter and check a measure from fact is not blank