Filters - Relevant Data Only?

[hgromek]

I have 3 fact tables with 1 to many relationships to my data table. On the Filters pane, I want the fact table choices to only show the data relevant to the data table. 

For example 

Data Table: Sales of colored t-shirts. relationship based on colorID. 

Fact Table: Colored t-shirts. (ColorID and Color) 

in the data, there are only red and blue sales, but in the colored t-shirts table, we have many color options Red, Blue, Yellow, Green. 

The current filter pane shows all colors (even with no sales). I would like it to only show Red and Blue since that is what's in the data.  

Is it possible? Do I need to just merge all the tables into 1? What's the point of the relationships then? 

[v-yangliu-msft · Accepted solution]

Hi  @hgromek ,

I created some data:

Data Table:

Fact Table:

Here are the steps you can follow：

1. Create measure.

Measure =
IF(
    MAX('Data Table'[ColorID]) in SELECTCOLUMNS('Data Table',"colorid",'Data Table'[ColorID]),1,0)

2. Place the measure in Filters, set is =1, apply filter.

3. Result:

Only display the content in the Data Table.

Best Regards,

Liu Yang

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

[amitchandak]

@hgromek , Create a measure like this and use that in visual level filter of you filter pane and check for non blank value 

Measure =  countrows(Data) + countrows(Fact)

measure <> blank in visual level filter of color 

This will make filter dependent of values of both tables

[hgromek]

Maybe I misunderstood the suggestion because it doesn't limit the filters. I also have the filters on either filter on-page or filters on all pages