How to pass Filters from One Dimension to another in Power BI Data Model

How to Filter One Dimension from another Dimension in Power BI Data Model
Scenario:
Let's suppose we have a Power BI Star Schema Data model like below:

The Relationships are as follows:

From the Data Model, we observed that dim_Products and dim_Country do not have the direct relationship.

However, they are having the (1:M) relationship with the fact table fct_Orders_Sample.

There are total 6 Products available in the dim_Products:

Please note that, some Products that do not have the sales in some Countries in the specific Months.

From the above Star Schema Data model, we need to Calculate the Count of Products sold from the dimension table dim_Products  at a Country Level.

In this Scenario we are Passing the Filters from the dim_Country dimension to dim_Products.

Now we will calculate the 2 Measures, one from Dimension Table (dim_Products) and another form the fact table(fct_Orders_Sample) to see the Filter behaviour.

Products Count (fact) = DISTINCTCOUNT(fct_Orders_Sample[Product_ID])

Products Count (dim) = DISTINCTCOUNT(dim_Products[Product_ID])

The result from fact table is Correct but not from Dimension table as shown below:

Now we can fix the measure based on dimension table(dim_Products), by adjusting the filter Context via passing the fact table into CALCULATE Function.

Products Count Adj (dim) =

CALCULATE(DISTINCTCOUNT(dim_Products[Product_ID]), fct_Orders_Sample)

The above measure can be written using the below CROSS Fiter approach as well, which is not a ideal method:

Products Count CrossFilter (dim) =

CALCULATE(DISTINCTCOUNT(dim_Products[Product_ID]),

    CROSSFILTER(dim_Products[Product_ID], fct_Orders_Sample[Product_ID], Both)

    )

As we know, some Products don't have the Sales, for which if you want to display the Zero, we can adjust the logic as per below:

Products Count Adj Zero (dim) =

VAR _ZeroSales = IF ( NOT ISBLANK(SELECTEDVALUE(dim_Country[Country_ID])),0)

VAR _ProdCount =  

    CALCULATE(

            DISTINCTCOUNT(dim_Products[Product_ID]), fct_Orders_Sample                 )

RETURN

_ProdCount + _ZeroSales

Result:

To enable the Filtering between Slicers from dim_Country to dim_Products, we need to pass a visual level filter for dim_Products slicer as "Products Count Adj Zero (dim) > 0" :

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------