Forum Discussion
Filters - Relevant Data Only?
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?
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
3 Replies
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
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
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
