Create a Single measure that can be used as filter to show only values/rows per fact table.

[v_mark]

I have a dimension table and 4 fact tables in my model. 

They are have a relationship of 1 to many . Everything works fine however whenever I drop a slicer to filter each of my fact table a value from my dimtable still exist I want to remove that and just show values existing on my fact tables. They are a couple of ways to do it. 

As shown here (40) Power BI One Slicer to filter other slicers based on Items that has value only without BI Direc...

My preferred choice is to create a measure. I can achieve that by creating 4 measure of countrows or sum per facttables and dropping that measure in my slicer and set a condition as filter on this visual greater than 0. 

But is there a way to create one measure instead of 4? TIA

[OwenAuger · Accepted solution]

Hi @v_mark 

I normally create a 0/1 measure that returns 1 for items to include on the slicer.

In the case of 4 fact tables, you could return 1 if at least one fact table is nonempty:

At Least One Fact Nonempty =
INT (
    NOT ISEMPTY ( FactTable1 )
    || NOT ISEMPTY ( FactTable2 )
    || NOT ISEMPTY ( FactTable3 )
    || NOT ISEMPTY ( FactTable4 )
)

This SQLBI article uses a similar approach.

Does this work as intended?

Regards,

Owen

[v_mark]

Thank you for sharing @OwenAuger.

For some reason 2 out of the 4 tables did not respond well on the measure and still shows unavailble data.

What I did is just use seperate measures instead. Anyway Thank you for the help!